Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LuisC
Starting Member
6 Posts |
 Posted - 2007-11-07 : 14:09:17
|
| I am new to MS SQL. I created a table and inserted an Identity fieldto generate a unique number for every row. I then proceeded toimport data into that new table hoping that when the import processfinished the Identity column would have a unique number. I firstplaced the identity field in the last position of the table but anerror showed saying that such field does not accept nulls.I then placed the identity field as the first column and it game meanother error.I would truly appreciate it if someone can explain to me how toimport data from an excel file into a table containing an identitycolumn, and for that column to generate a unique number once theimport process ends.Thanks |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-07 : 14:19:50
|
| Specify the Identity (seed,increment) as ID within the import itself.Refer to BOL for Identity information... |
 |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 15:01:14
|
| if the import is using tsql and openrowset use a column list that does not include the identity column. if the import is using dts, treat the identity column like it does not exist when mapping columns. |
|
|
|
LuisC
Starting Member
6 Posts |
Posted - 2007-11-07 : 17:07:46
|
| I am using the DTS wizard. I tried to import the records into the table having the identity in the 1st column and the import process attempts to insert the first column of the data in excel into the identity field. If I move the identity field to the last column and try to import the data it says that such column does not accept nulls. |
|
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-07 : 17:47:42
|
| the error about nulls means in the mapping you are trying to supply values to the destination identity field. when you run through the wizard pick the source and destination. then click the "..." for the transformation. make sure the enable identity insert is not checked and in the mappings list the destination field that is the identity field has a source field of "<ignore>". |
|
|
|
LuisC
Starting Member
6 Posts |
Posted - 2007-11-09 : 10:29:34
|
| I can thank you enough for your great help I just tried your suggestion and it worked just fine. Thanks again. |
|
|
|
LuisC
Starting Member
6 Posts |
Posted - 2007-11-09 : 10:30:23
|
| I can’t thank you enough for your great help I just tried your suggestion and it worked just fine. Thanks again |
|
|
|
|
|
|
Impoting data from excel and the Identity column