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. First, you can change the column that holds the identity.

SELECT DepartmentID+0 AS DepartmentID, Name, GroupName, ModifiedDate
INTO test
FROM HumanResources.Department
WHERE 1=0

That works great, and yes you want to list the fields in your query, but in this case, where you are creating an identical copy of the table it’s really not necessary and if there are a large number of columns it’s a real pain too. (Not that you can’t just pull the column list from the object explorer.) So how do we do it without specifically listing all the columns? Well the source of this post was a tweet by Kalen Delaney (b/t).

Now a statement like that, of course, started a big discussion and with a clue from Adam Machanic (b/t) I was able to figure it out for myself. I should point out here that while I’m a big proponent of multiple people blogging about the same subject I did double check with Kalen before writing this up (her post was on a different subject but just used this trick). If she’d been planning on writing about this particular trick I’d have either waited a few months or referenced her post and just given my opinions.

Ok, so what’s the trick? UNION ALL

FYI I prefer UNION ALL over UNION because UNION does a distinct and if you are going to be including the data when you create the new table with the SELECT you could end up with incorrect data or at the very least the query will be slower.

If you want the table to be empty use the WHERE 1=0 or TOP (0) on both sides of the UNION ALL. If you want a copy of the table with data then just put the WHERE 1=0 or TOP (0) on one side.

SELECT *
INTO test
FROM HumanResources.Department
WHERE 1=0
UNION ALL
SELECT *
FROM HumanResources.Department
WHERE 1=0

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL