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:
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.
Post a Comment