#0275 – SQL Server – IDENTITY columns – Myths – IDENTITY columns do not propagate via SELECT…INTO statements

As I was writing the series on myths around IDENTITY columns, I started to wonder whether copying a table into another using the SELECT…INTO statement copies over the IDENTITY property also.

The simplest way to answer this question was to run a simple test, which is shown below:

  1. Create a table with an IDENTITY column defined, and insert some test data
  2. Use SELECT…INTO to select data from the source table and pump it into another table

    • For clarity, I will be using different column names between the source and the destination columns

  3. Use the catalog views – sys.columns and sys.identity_columns to confirm whether the new table was created with the IDENTITY column in place or not

USE tempdb;


--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
    DROP TABLE dbo.tIdentity;

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)

--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),

--2. Use SELECT..INTO to bulk insert data to a new table
SELECT SourceTable.IdentityId AS DestinationId,
       SourceTable.IdentityValue AS DestinationValue
INTO dbo.DestinationTable
FROM dbo.tIdentity AS SourceTable

--3. Check the properties of the new column - is it an IDENTITY column?
SELECT sc.name AS ColumnName,
       OBJECT_NAME(sc.object_id) AS TableName,
       sic.seed_value AS IdentitySeed,
       sic.increment_value AS IdentityIncrement,
       sic.is_nullable AS IsNullable,
       sic.last_value AS LastIdentityValueUsed
FROM sys.columns AS sc
INNER JOIN sys.identity_columns AS sic ON sc.object_id = sic.object_id
                                      AND sc.column_id = sic.column_id
WHERE ( sc.object_id = OBJECT_ID(N'dbo.DestinationTable',N'U') OR
        sc.object_id = OBJECT_ID(N'dbo.tIdentity',N'U')
  AND sc.is_identity = 1;

--4. Cleanup
IF OBJECT_ID('dbo.tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;

IF OBJECT_ID('dbo.DestinationTable','U') IS NOT NULL
    DROP TABLE dbo.DestinationTable;

ColumnName     TableName         IdentitySeed IdentityIncrement IsNullable LastIdentityValueUsed
-------------- ----------------- ------------ ----------------- ---------- ---------------------
IdentityId     tIdentity              1              1               0             3
DestinationId  DestinationTable       1              1               0             3

As can be seen from the experiment, the IDENTITY property propagates from one table to another via the SELECT…INTO clause.

Until we meet next time,

Be courteous. Drive responsibly.


3 thoughts on “#0275 – SQL Server – IDENTITY columns – Myths – IDENTITY columns do not propagate via SELECT…INTO statements

  1. Pingback: #0390 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Keep Identity option | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.