March 19, 2009 at 10:14 pm
Dear Forum,
First of all, I would like to thank you for your generous help in the past, and for looking at my current issue.
I am trying to import an excel file into a table in sql server 2000.
The problem is, my Id column in the database does not allow nulls and It is not auto filling.
The columns in my excel file are: Pw, Sent, Downloaded
The Columns in my db table are: Id, Pw, Sent, downloaded
I am hoping to get the Id column in my table to autofill from 2 to 5000 as these rows are added. But it is giving me the error:
Error at Destination for Row number 199. Errors encountered so far in this task:1. The statement has been terminated. Cannot insert the value NULL into column 'Id', table 'simplestereo.dbo.songdload1';column does not allow nulls. INSERT fails.
I am using the DTS Import Export Data Wizard. When I click on the "Transform" button, and go to the "Column Mappings and Transformations" screen, it says that my Id column has no source and should be Ignored. What I would like to do is have that autofill the number incrementally in the Id column in the database.
See Screenshot: http://www.iconconcerts.com/misc/columnmappings.jpg
Adding an Id column in my excel document with numbers 2 though 5000 would not work well because what if I want to import more rows later? It would be weird to make the second excel spreadsheet I import have an Id column that starts at 5001 and ends at 10,000.
So my question is, how do I make the database Id column autonumber as it imports these in? Similar to how it autonumbers when I just do a simple Insert into the database.
Here is a screenshot of my table design: http://www.iconconcerts.com/misc/columndesign.jpg
I appreciate your help, or any advice you have. I thought I was able to do this a year ago. But it is not working now..
Thanks again for your help,
Jeff
Boise, ID
March 19, 2009 at 11:58 pm
Make the id column as identity and start the values from 2 with an increment of 1.Use dbcc checkident reseed to acheive this.
Then import the data from the excel normally and after its done remove the identity property of the id column by using the table design wizard.
Hope this helps...
March 20, 2009 at 1:00 am
SQL Reddy (3/19/2009)
Make the id column as identity and start the values from 2 with an increment of 1.Use dbcc checkident reseed to acheive this.Then import the data from the excel normally and after its done remove the identity property of the id column by using the table design wizard.
Hope this helps...
This can be problematic. What about the code changes that has to be done to the applications that insert data into the table? Currently they need to insert value into the table, but if he’ll change it to be an identity column, the code will generate run time error (not to mention that the original poster will also have to make sure that no one tries to update the ID). The 2 options that I can think about are importing the data into a staging table that will have an identity column, and then insert the data from the staging table into the real table or adding the ID column to the excel file.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2009 at 8:08 pm
Thanks for your advice. I just tried it again same as always, and it worked this time. Maybe my enterprise manager was having some kind of problem. Wierd.
But thanks anyway.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
