Empty table structure v7.0
In previous SQL TechMails, we discussed how to copy data from one table into
another using the INTO clause in a SELECT statement. We can further expand upon
this statement to create a table structure with no data. Using an invalid WHERE
clause in a SELECT INTO statement will still carry out the command but will not
bring over any data into the new table.
SELECT * INTO TableX_Temp FROM TableX WHERE 1 = 2
The above statement will copy the structure of TableX to TableX_Temp without
copying any data over to TableX_Temp. Since one does not equal two, the WHERE
clause portion cannot be fulfilled. However, SQL Server knows nothing about this
validity at run time and therefore carries out the statement as if there is data
to be moved into TableX_Temp.
You can also use this invalid WHERE clause to return the column names in a
SELECT statement.
SELECT * FROM TableX WHERE 1 = 2
This would return only the column headers from TableX without any data, as in
the example below.
SSN FName LName Address
---------- ---------------- --------------- ----------------------
This method is much faster than using the SET FMTONLY ON/OFF option and has
the advantage of being specific only to the command issued rather than the
entire session.
Home Previous Next