Create an empty table from a SELECT statement but without the IDENTITY.
3August 28, 2017 by Kenneth Fisher
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
[…] Kenneth Fisher shows a couple of ways to remove an identity property from a column when creating a n…: […]
[…] you checked out Kenneth Fisher’s ( Blog | Twitter ) recent blog post about creating an empty table from a SELECT statement but without the identity? It’s a good post and I especially like the fact that he was able to figure it out […]
I have an alternative action I’ve used. I capture the results of merge statements into an output table. So, I like capturing the $action value as well. I do this. select id = 0, *, convert(varchar(10). null) [action] into #output from dbo.AffectedTable. Now I drop the identity column and rename the “id” column and I’m ready to go with an output inserted.*, $action into #output; I’ll definitely give the union all approach a try. It’s pretty straight forward and can accommodate an Action column as well.