How do I copy a table with SELECT INTO but ignore the IDENTITY property?

Question

I have a table with identity column say:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

It's well known, that this

select * into copy_from_with_id_1 from with_id;

results in copy_from_with_id_1 with identity on id too.

The following stack overflow question mentions listing all columns explicitly.

Let's try

select id, val into copy_from_with_id_2 from with_id;

Oops, even in this case id is an identity column.

What I want is a table like

create table without_id (
 id int,
 val varchar(30)
);

Answer 1

From Books Online

The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

• The SELECT statement contains a join, GROUP BY clause, or aggregate function.
• Multiple SELECT statements are joined by using UNION.
• The identity column is listed more than one time in the select list.
• The identity column is part of an expression.
• The identity column is from a remote data source.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.

Answer 2

Inspired by Erics answer, I found the following solution which only depends on the table names and doesn't use any specific column name :

select * into without_id from with_id where 1 = 0
union all
select * from with_id where 1 = 0
;
insert into without_id select * from with_id;

Edit

It is even possible to improve this to

select * into without_id from with_id
union all
select * from with_id where 1 = 0
;

Answer 3

You can use a join to create and populate the new table in one go:

SELECT
  t.*
INTO
  dbo.NewTable
FROM
  dbo.TableWithIdentity AS t
  LEFT JOIN dbo.TableWithIdentity ON 1 = 0
;

Because of the 1 = 0 condition, the right side will have no matches and thus prevent duplication of the left side rows, and because this is an outer join, the left side rows will not be eliminated either. Finally, because this is a join, the IDENTITY property is eliminated.

Selecting just the left side columns, therefore, will produce an exact copy of dbo.TableWithIdentity data-wise only, i.e. with the IDENTITY property stripped off.

All that being said, Max Vernon has raised a valid point in a comment that is worth keeping in mind. If you look at the execution plan of the above query:

you will notice that the source table is mentioned in the execution plan just once. The other instance has been eliminated by the optimiser.

So, if the optimiser can correctly establish that the right side of the join is not needed in the plan, it should be reasonable to expect that in a future version of SQL Server it may be able to figure out that the IDENTITY property need not be removed either, since there is no longer another IDENTITY column in the source row set according to the query plan. That means that the above query might stop working as expected at some point.

But, as correctly noted by ypercubeᵀᴹ, so far the manual has explicitly been stating that if there is a join, the IDENTITY property is not preserved:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless [...] [t]he SELECT statement contains a join.

So, as long as the manual keeps mentioning it, we can probably rest assured that the behaviour will stay the same.

_{Kudos to Shaneis and ypercubeᵀᴹ for bringing up a related topic in chat.}

Answer 4

Try this code..

SELECT isnull(Tablename_old.IDENTITYCOL + 0, -1) AS 'New Identity Column'
INTO   dbo.TableName_new
FROM   dbo.TableName_old 

The ISNULL call ensures that the new column is created with NOT NULL nullability.

Answer 5

Just to show a different way:

You can use a linked server.

SELECT * 
INTO without_id 
FROM [linked_server].[source_db].dbo.[with_id];

You can temporarily create a linked server to the local server using this:

DECLARE @LocalServer SYSNAME 
SET @LocalServer = @@SERVERNAME;
EXEC master.dbo.sp_addlinkedserver @server = N'localserver'
    , @srvproduct = ''
    , @provider = 'SQLNCLI'
    , @datasrc = @LocalServer;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'localserver'
    , @useself = N'True'
    , @locallogin = NULL
    , @rmtuser = NULL
    , @rmtpassword = NULL;

At which point, you'd run the select * into code, referencing the localserver linked server four-part-name:

SELECT * 
INTO without_id 
FROM [localserver].[source_db].dbo.[with_id];

After that completes, clean up the localserver linked server with this:

EXEC sp_dropserver @server = 'localserver'
    , @droplogins = 'droplogins';

Or, you could use OPENQUERY syntax

SELECT * 
INTO without_id 
FROM OPENQUERY([linked_server], 'SELECT * FROM [source_db].dbo.[with_id]');

Answer 6

The identity property isn't transferred if the select statement contains a join, and so

select a.* into without_id from with_id a inner join with_id b on 1 = 0;

will also give the desired behaviour (of the copied id column to not keep the IDENTITY property. However, it will have the side effect of not copying any row at all! (as with some other methods) so you'll then need to do:

insert into without_id select * from with_id;

(thanks AakashM!)

Answer 7

The easy way is to make the column part of an expression.

Example:
If table dbo.Employee has an identity on ID column then in the example below temp table #t will have an IDENTITY on ID column as well.

--temp table has IDENTITY
select ID, Name 
into #t
from dbo.Employee

Change this to apply an expression to ID and you #t will no longer have an IDENTITY on ID column. In this case we apply a simple addition to the ID column.

--no IDENTITY
select ID = ID + 0, Name 
into #t
from dbo.Employee

Other examples of expressions for other data types could include: convert(), string concatenation, or Isnull()

Answer 8

Sometimes, you want to insert from a table where you don't know (or care) if the column was created using IDENTITY or not. It may not even be an integer column that you are working with. In this case, the following will work:

SELECT TOP(0) ISNULL([col],NULL) AS [col], ... INTO [table2] FROM [table1]
ALTER TABLE [table2] REBUILD WITH (DATA_COMPRESSION=page)
INSERT INTO [table2] ...

ISNULL will drop the IDENTITY attribute from the column but insert it with the same name and type as the original column and also make it not nullable. TOP(0) will create an empty table that you can then use to insert selected rows into. You can also make the table compressed before you insert data if required.

Answer 9

select convert(int, id) as id, val 
into copy_from_with_id_without_id 
from with_id;

will remove identity.

The downside is that id becomes nullable but you could add that constraint.

Answer 10

You don't. select * into preserves the identity.