Thursday, 16 June 2011

Copying table structure in SQL Server

Why didn't I know this? For so many years I've bemoaned the fact that SQL Server doesn't have MySQL's CREATE TABLE LIKE statement:

CREATE TABLE NewTable LIKE ExistingTable
Um, er, except it does:
SELECT TOP 0 INTO NewTable FROM ExistingTable
The INTO clause of SELECT...
"...creates a new table in the default filegroup and inserts the resulting rows from the query into it." (MSDN link)
And of course, if you use TOP 0, there are no rows — but the table structure still gets created. (Naturally, if you want to copy the data as well, just leave off the TOP 0.)
Sometimes, you think you know something doesn't exist, and it turns out you just haven't looked hard enough to find it...
Happy coding,

1 comment:

Joe said...

One key thing to note: this shortcut does not duplicate the keys/indexes from the original table. It's a shortcut to create a table "clone", and i've used it often. But it should be used with caution where indexes are essential to the data integrity.