SELECT INTO behavior and the IDENTITY property

From Microsoft:

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.

You could use the IDENTITY function as they suggest and omit the IDENTITY column, but then you would lose the values, as the IDENTITY function would generate new values and I don't think that those are easily determinable, even with ORDER BY.

I don't believe there is much you can do, except build your CREATE TABLE statements manually, SET IDENTITY_INSERT ON, insert the existing values, then SET IDENTITY_INSERT OFF. Yes you lose the benefits of SELECT INTO, but unless your tables are huge and you are doing this a lot, [shrug]. This is not fun of course, and it's not as pretty or simple as SELECT INTO, but you can do it somewhat programmatically, assuming two tables, one having a simple identity (1,1), and a simple INNER JOIN:

    SET NOCOUNT ON;DECLARE    @NewTable SYSNAME = N'dbo.People_ExactCopy',    @JoinCondition NVARCHAR(255) = N' ON p.Name = r.Name';DECLARE    @cols TABLE(t SYSNAME, c SYSNAME, p CHAR(1));INSERT @cols SELECT N'dbo.People', N'Id', 'p'    UNION ALL SELECT N'dbo.ReverseNames', N'Name', 'r';DECLARE @sql NVARCHAR(MAX) = N'CREATE TABLE ' + @NewTable + '(';SELECT @sql += c.name + ' ' + t.name     + CASE WHEN t.name LIKE '%char' THEN         '(' + CASE WHEN c.max_length = -1             THEN 'MAX' ELSE RTRIM(c.max_length/            (CASE WHEN t.name LIKE 'n%' THEN 2 ELSE 1 END)) END         + ')' ELSE '' END    + CASE c.is_identity    WHEN 1 THEN ' IDENTITY(1,1)'     ELSE ' ' END + ',    '    FROM sys.columns AS c     INNER JOIN @cols AS cols    ON c.object_id = OBJECT_ID(cols.t)    INNER JOIN sys.types AS t    ON c.system_type_id = t.system_type_id    AND c.name = cols.c;SET @sql = LEFT(@sql, LEN(@sql)-1) + ');SET IDENTITY_INSERT ' + @NewTable + ' ON;INSERT ' + @NewTable + '(';SELECT @sql += c + ',' FROM @cols;SET @sql = LEFT(@sql, LEN(@sql)-1) + ')    SELECT ';SELECT @sql += p + '.' + c + ',' FROM @cols;SET @sql = LEFT(@sql, LEN(@sql)-1) + '    FROM ';SELECT @sql += t + ' AS ' + p + '     INNER JOIN ' FROM (SELECT DISTINCT        t,p FROM @cols) AS x;SET @sql = LEFT(@sql, LEN(@sql)-10)     + @JoinCondition + ';SET IDENTITY_INSERT ' + @NewTable + ' OFF;';PRINT @sql;

With the tables given above, this produces the following, which you could pass to EXEC sp_executeSQL instead of PRINT:

CREATE TABLE dbo.People_ExactCopy(    Id int IDENTITY(1,1),    Name varchar(10) );SET IDENTITY_INSERT dbo.People_ExactCopy ON;INSERT dbo.People_ExactCopy(Id,Name)    SELECT p.Id,r.Name    FROM dbo.People AS p     INNER JOIN dbo.ReverseNames AS r      ON p.Name = r.Name;SET IDENTITY_INSERT dbo.People_ExactCopy OFF;

I did not deal with other complexities such as DECIMAL columns or other columns that have parameters such as max_length, nor did I deal with nullability, but these things wouldn't be hard to add it if you need greater flexibility.

In the next version of SQL Server (code-named "Denali") you should be able to construct a CREATE TABLE statement much easier using the new metadata discovery functions - which do much of the grunt work for you in terms of specifying precision/scale/length, dealing with MAX, etc. You still have to manually create indexes and constraints; but you don't get those with SELECT INTO either.

What we really need is DDL that allows you to say something like "CREATE TABLE a IDENTICAL TO b;" or "CREATE TABLE a BASED ON b;"... it's been asked for here, but has been rejected (this is about copying a table to another schema, but the same concept could apply to a new table in the same schema with a different table name). http://connect.microsoft.com/SQLServer/feedback/details/632689

I realize this is a really late response but whoever is still looking for this solution, like I was until I found this solution:

You can't use the JOIN operator for the IDENTITY column property to be inherited.What you can do is use a WHERE clause like this:

SELECT a.*INTO NewTableFROM MyTable aWHERE EXISTS (SELECT 1 FROM SecondTable b WHERE b.ID = a.ID)

This works.