Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
);
share|improve this question
    
you can use select * into copy_from_with_id_1 from with_id union with_id; and that ignore identity –  Filipe May 14 at 14:42

5 Answers 5

up vote 20 down vote accepted

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
select 0, 'test_row' from sys.tables where 1 = 0;
share|improve this answer
    
pretty cool workaround. –  Mladen Prajdic Jan 26 '11 at 21:24
2  
+1 nice sql hack –  yrushka Jan 27 '11 at 10:09
2  
+1: I'd add that it is important to comment in code why this code is in place, lest it be removed the next time someone looks at it. :) –  Robert Jeppesen Feb 18 '11 at 21:33
    
+1 it was cool. –  garik Dec 13 '11 at 9:34
    
PS you don't need the sys.tables part. you can just do select 0, 'test_row' where 1 = 0; –  John Gibb Feb 21 at 20:40

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.

share|improve this answer

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
;
share|improve this answer
    
+1: Cleaner looking solution that works the same. –  Robert Jeppesen Feb 18 '11 at 21:32
    
This works for me on MS SQL server 2008. –  Daniel Allen Langdon Nov 21 '13 at 20:39

Just to show a different way:

You can use a linked server. But this requires a second sql-server, you can't create a linked server to yourself.

select * into without_id from [linked_server].[source_db].dbo.[with_id];

or you can use OPENQUERY sntax

SELECT * into without_id FROM OPENQUERY([linked_server], 'Select * from [source_db].dbo.[with_id]');
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.