Easily create copies of SQL Server tables—without copying their data

It's not terribly uncommon to need to make a copy of the structure of a table (usually temporarily). If you need a copy of the data also, you can simply use a  SELECT INTO, like this:

SELECT * INTO Employees2 FROM Employees (NOLOCK)

In this case, SQL Server creates a new table, Employees2, which matches the exact structure of the Employees table, and copies all the data into it. If you don't need the data in the new table, you can always TRUNCATE Employees2.

But even easier (and usually faster) is not to copy the data in the first place. To accomplish this in one step, here's a clever trick—use the SELECT INTO syntax with a WHERE clause that returns no rows, as shown here:

SELECT *
INTO Employees2
FROM Employees (NOLOCK)
WHERE 1 = 0

Even though the query returns no rows, SQL Server will still create the second table! You can also use the same syntax to create a temp table that matches an existing table structure. Just put a pound sign (#) in front of the new table name. Would you ever want to do this with a table variable? No, because the whole point of this technique is that you don't have to know the structure of the table in advance; yet, to use a table variable, you would have to know, and declare, its structure—which really defeats the whole purpose.