Selecting Into Tables, Sans Identity

Kevin Feasel



Kenneth Fisher shows a couple of ways to remove an identity property from a column when creating a new table:

A while back I did a post about creating an empty table using a SELECT statement. Basically doing something like this:

SELECT TOP 0 * INTO tableNameArchive FROM tableName

will create a new table with the exact same structure as the source table. It can be a really handy way to create an archive table, a temp table, etc. You don’t create any of the extra objects (indexes, triggers, constraints etc) but what you do end up with is every table property from the original table. This includes datatypes, nullability, and (as I’m sure you realized from the title) IDENTITY. Which if you are creating an archive table, a temp table, etc is probably not something you want. Fortunately, there are two easy ways to get rid of the identity.

Click through to see those two methods.

Related Posts

Bitwise Logic To Make Values Negative

David Fowler is working with arcane magic: The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT. If you […]

Read More

Executing Dynamic SQL: EXEC vs sp_executesql

Tara Kizer gives a scenario where using sp_executesql can result in lower CPU utilization than EXEC: For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated […]

Read More


August 2017
« Jul Sep »