Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Problems With IDENTITY() and the DataPump task.
By Allan Mitchell
Version 7.0
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7

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.

    Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7