Wednesday, September 18, 2013

Using SSIS to Insert Data into a table with an Identity Column


If you are trying to insert data into a table that has an identity column you have two options. Use the ID that is generated by the SQL table or use an ID that is on the incoming data. Let's look at both of these options.
I have a table name IDTest and it has two columns, called "ID" and "Name".  The "ID" column is the identity column. The "Name" column is and varchar 100. The script to create this table is:
CREATE TABLE [dbo].[IDTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NULL
) 
The incoming data is a delimited flat file with the following data:
Id,Name
2,Bob
4,John
6,Dusty
8,Mike
10,Brian
12,Devin
In the first scenario we are loading data with only the names. We want the SQL table to generate an ID. You simply DO NOT connect a column to the id column, this way SQL will generate the ID automatically.
SSIS Insert into Identity Column

No comments:

Post a Comment