December 7, 2005 at 1:47 pm
Hers is the situation:
1. You have a table with an ID field set as a primary key and "identity" set to "yes" - sample below:
ID product price
1 x 2.50
2 y 3.00
3 z 3.50
Now for the issue:
1. I copy the table ONLY from the origination server to the destination server.
2. When I import the data to the destination server from the origination server, the new records have ID's of 4, 5 and 6 - how do I deal with this?
Needless to say, I am new to DTS packages.
Maybe I am doing this totally wrong - please give me some direction.
Thanks, in advance, for your assistance.
Gary
December 7, 2005 at 3:13 pm
First off, is the Origination server have the data of record? meaning, there will NEVER be inserts into the destination server table from any other source than this dts package?
If yes then you just need to set identity insert option in the Options tab to on.
To do this double click the data pump task, then go to the options tab, its in the bottom section.
December 7, 2005 at 3:20 pm
how do you copy data from one server to another?
I assume the destination table has identity set on ID column.
Couple ways to get around - though maybe not the best ways.
1. SET IDENTITY_INSERT tablename ON
copy/insert....
SET IDENTITY_INSERT tablename OFF
2. before insert alter table and modify ID column to non identity
after insert alter table and modify ID column back to identity (assume ID will always be unique)
December 12, 2005 at 3:41 pm
If you are creating the dts from the Import/Export Wizard and have chosen "Copy table(s) and view(s) from the source database", when you select the table(s) click on the Transform button and make sure that "Enable identity insert" is checked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply