SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Create an empty table from a SELECT statement but without the IDENTITY.

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...