Just recently we have seen a number of posts in the newsgroups concerning problems with
IDENTITY columns when inserting data into a table using DTS. This walkthrough should hopefully explain
most of the problems we can encounter and give you some useful information about behind the scenes stuff as well.
N.B. Environment SQL Server 2000 SP3
Creating our destination table
CREATE TABLE MyIdentityTableDestination
(
IdentityColumn INTEGER IDENTITY(1,1) PRIMARY KEY,
MyCharacterCol varchar(100)
)
Creating our source table
CREATE TABLE MyIdentitySource
(
SourceID INTEGER Primary Key,
SourceCharacterCol varchar(100)
)
Insert some sample source data
INSERT MyIdentitySource VALUES(1,'A')
Now we have our source and destination tables, we want to create a
DataPump task that transfers the data from the source table (MyIdentitySource)
into the destination (MyIdentityTableDestination) table.
There are two possible outcomes we want.
The data in the source table's SourceCharacterCol needs to be inserted
into the destination table's MyCharacterCol column.
We want to autogenerate new values in the destination table's MyCharacterCol column.
Over the next few pages we will explore these requirements and finish up with
some places to look if things go awry.