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.

Over the next few pages we will explore these requirements and finish up with some places to look if things go awry.