Identity Seedgreenspun.com : LUSENET : SQL Server Database Administration : One Thread
Eric, I am trying to put together a data warehouse using surrogate keys. I have defined this field as a primary key and with the following properties: Column Name: SKSeedCustID Data Type: int Length: 4 Description: Blank Identity: Yes Identity Seed: 1 Identity Increment: 1 Formula: Blank
The problem arises when using DTS I try to import data from a text file everything is aligned field for field. The system tries to add all rows with out incrementing the primary key field until all rows are added. This causes an error since the PK cannot be null it won't complete the process. I have tested this by inputing rows from the keyboard and the problem is much the same. I can enter a number of rows but as I complete one row and move to the next the Identity Column(PK) does not increment until you exit the table, then when you re-open the table the Column is populated on a one-up basis.
Have I set up the Identity Column wrong or is this a known issue that I can't get around?
Thanks in advance for your help..
-- Anonymous, August 03, 2000
What can I say? It worked for me.
When I set up a data file to be read in using a DTS bulk insert task, it worked just fine. To do this, I created a bulk copy format file which does not include the identity column since the identity column is generated when the row is added. Naturally, the identity column is not included in the text file.
If you want to import the identity column value from the text file, you will need to put a checkmark in the 'Enable identity insert' box on the Options tab of the Bulk Insert Properties dialog for the Bulk Insert Task of your DTS package. You will also need to assure that you do not attempt to insert two rows with the same identity column.
-- Anonymous, August 10, 2000