Using DTS to combine 5 tables to one table

[JenniferCheng]

I use 5 DTS packages to combine 5 tables (T1, T2, …, T5) of 5 databases to one table Sam. All these 5 tables have same primary key S_code and value of S_code is from 1 to 200. I use DTS to transport record in 5 tables to Table Sam. Since there is duplicate S_code, after run first DTS to transport table T1 to Table Sam, I can not run other DTS packages successfully.

How can I change S_code value when using DTS, such as: for T1, set S_code = S_code+ 1000; for T2, set S_code = S_code + 2000; for T3, set S_code = S_code + 3000; for T4, set S_code = S_code + 4000; for T5, set S_code = S_code + 5000;
If I don’t change S_code value, how can I combine 5 tables to one table?

Thanks.
Jennifer

 

[JayKusch]

Can you add a new Identity column as you Primary Key for the Sam_Table?

Keep the S_code field of the Sam_Table, just do not make it a Primary Key. This way you can keep the original S_code from the source table in case you need to refere back to it.

By setting the NEW Primary Key as an Indentity Column, you can let SQL Server take care of incrementing the field for you.

Then you should be able to port all 5 tables in.

Thanks

J. Kusch

 

[JenniferCheng]

Thanks very much.

By this way, do I need make S_code non-primary-key in the source tables?

Thanks.

Jennifer

 

[JayKusch]

Your choice.

Use the newly created Identity Column as the PK or add the S_code w/ the Identity Column as a covered key.


Thanks

J. Kusch