INF: How to Use DTS Wizard to Insert Data into SQL Tables with an Identity Column (246310)

SUMMARY

The Data Transformation Services (DTS) Wizard provides a method to insert data into a table that contains an identity column from a source, provided the data contains unique integer values and does not violate constraints imposed on the destination table. For more information, see the first example in the More Information section.

The DTS Wizard also provides a method to insert into a table containing an identity column if the source data DOES NOT contain the unique identity values. In this scenario, non-identity columns only can be the destination from a DTS source. This can be achieved by modifying the DTS package column mappings and by clearing the identity insert option. For more information, refer to the second example in the More Information section.

Executing the package directly, in the second example, without first modifying the package column mappings, results in this error:

DATA TRANSFORMATION SERVICES: Data Pump Exception Log

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Cannot insert the value NULL into column 'ID', table 'Test.dbo.T2'; column does not allow nulls. INSERT fails.

MORE INFORMATION

First Example

This example inserts into a table containing an identity column from a DTS data source containing the unique values.

Create a table named T1 in Microsoft Access 97 with these two fields:
Field1 = ID (PK and a datatype of auto number)
Field2 = Desc (Text)

-- Insert some dummy rows into the Microsoft Access table.

Create a table named T1 in SQL 7.0 as follows:

CREATE TABLE [dbo].[T1]
	[ID] [int] IDENTITY (1, 1) NOT NULL,
	[Desc] [char] (10) NULL
) ON [PRIMARY]
GO

Create the DTS Package to import from the Microsoft Access 97 table.
Go to the Tools menu, choose Data Transformation Services, Import Data, and then select Next.
Choose a Data Source screen. Select Microsoft Access, and fill in the path and name of the Access database that contains table T1.

Click Next and choose a destination database in SQL. Click Next again. Make sure that Copy Table(s) from Source Database is selected and then choose Next.

The DTS Import Wizard screen named Select Source Tables prompts you to select the source table from Microsoft Access 97 and choose a destination table in SQL named T1.
On the same screen press the Build ... tool in the transform column and make sure the ENABLE IDENTITY INSERT option is selected. This allows you to insert the rows, provided they are unique and are allowed by the constraint or the datatype chosen in the SQL Table. Make sure that Append Rows to destination table is selected. Choose OK, and then press Next.
Select Run Immediately and also select Save DTS Package. Press Next.
Choose a name for your DTS package and press Next. The import process is now complete.

Second Example

This example inserts DTS data into non-identity columns only, and allows the SQL Server to automatically populate the identity column with the next value.

Create a table named T2 in Microsoft Access 97 with 1 field:
Field = Desc (Text)

-- Insert some dummy rows into the Microsoft Access table.

Create a table named T2 in SQL 7.0 as follows:

CREATE TABLE [dbo].[T2]
	[ID] [int] IDENTITY (1, 1) NOT NULL,
	[Desc] [char] (10) NULL
) ON [PRIMARY]
GO

Create the DTS Package to import from the Microsoft Access 97 table.
Go to the Tools menu, choose Data Transformation Services, Import Data and then click Next. Choose a Data Source screen, choose Microsoft Access, and fill in the path and name of the Access database containing table T2.
Choose a Data Source screen. Select Microsoft Access and fill in the path and name of the Access database containing table T1.

Click Next and choose a destination database in SQL. Click Next again. Make sure that Copy Table(s) from Source Database is selected and then choose Next.

The DTS Import Wizard screen named Select Source Tables prompts you to select the source table from Microsoft Access 97 and choose a destination table in SQL named T2.
On the same screen select the Build... tool in the transform column and make sure the ENABLE IDENTITY INSERT option is NOT selected. This allows SQL Server to insert the rows automatically into the identity column. Make sure that Append Rows to destination table is selected.
The column mappings are on the grid at the bottom of the screen. Note, that the wizard will not map the columns correctly because you are providing SQL with a 1 column source and attempting to populate a 2 column destination.

To map the columns correctly, on the first row, change the source to <Ignore> and ensure the destination is ID (the identity column). On the second row, change the source from <Ignore> to Desc (the description column) and match it to the Desc column in the destination table. Choose OK, and then press Next.
Select Run Immediately and also select Save DTS Package. Press Next.
Choose a name for your DTS package, press Next and the import process is now complete.