SQL DTS Import and Identity Seed

[BunnyBoiler]

I have a table with an int primary key. Identity is not set on as the application using the table doesn't like it.
I need to write a DTS package that will import data into the table - but I need the import to work as though identity is on. I can't switch it on the table, do the import and then switch it off as this will upset users.
Any ideas how I can design my DTS to import records with the primary key column being updated with the next number for each new record?

B Boiler

 

[JohnDTampaBay]

How about using a a staging table? Create a staging table with the same structure as your destination table, but with an IDENTITY column. Seed the identity with the first value. Import into the staging table, the identity being populated for you. Then insert into the destination table from the staging table. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[TomCarnahan]

Leave the primary key off, drop the table and re-create it with the DTS package, then do an ALTER TABLE ALTER COLUMN to add the PK with identity (ALL within the DTS package).

Sorry I don't have my example on hand. I just did it today and it worked!

--- Tom

 

[pkailas]

Are you trying to insert data into the INT column while it's set to Identity? If so, that won't work. If you need to insert into that column, just make another column as your identity column and let SQL update it.

 

[TomCarnahan]

I found a solution!

The DTS package does the following in order:

- DROP the Destination table
- CREATE the Destination table w/o PK or IDENTITY
- CREATE PK w/IDENTITY
- INSERT from Source to Destination

... the PK is autonumbered.

Everyone, thanks for your input ... your ideas were the catalyst that lead me to this solution which worked perfectly for what I am trying to do.

-- Tom


--- Tom