Importing Data into SQL Table

Question

Hi,

I have SQL Server 2005 full installation. I'm just starting with SQL, so bear with me.

I created a sample DB with a sample table in it. I wanted to populate this table from the Excel spreadsheet, so I used Management Studio to do that. I right clicked on the DB name, selected Tasks --> Import Data. Then I selected MS Excel as Data Source, browsed to my file, picked SQL Native Client from the destination drop down list, picked my server and DB name and start importing.

The data in the Excel spreadsheet is organized the way that 2nd column may or may not have the same exact value as the 1st column. Moreover, if the 1st column is integer all the time, the 2nd column is not (there are integer values and character values but overall type is just character).  When SQL imports the data, whenever data in the 1st and 2nd column is the same, it puts NULL in the 2nd column of the DB table. When they're different, it puts the correct values. I tried so many different things (changing data type in the Excel spreadsheet, changing data types in the SQL table) and nothing worked. I even tried Business  Intelligence Development Studio and wasn't even able to import anything.

Does anyone know why SQL ignores what it considers to be duplicate values in different columns?

TIA,

-Tony.

Answer 1

SSIS (which is behind the import/export wizard) uses the first 8 rows to determine the data type of an excel sheet you are importing. Consider using IMEX=1 in extended properties (see e.g. http://msdn.microsoft.com/en-us/library/ms141683.aspx)

PS notice this is the data mining discussion forum, please consider posting this question in the Integration Services forum

---

Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net