T-SQL: Renaming a Table Containing Data; Saving/Restoring Data from a Dropped Table; SELECT INTO

Sometimes is is better to DROP a table instead of using the ALTER TABLE or sp_rename commands. If the table has data in it, use the SELECT INTO syntax to create a temporary table. For example:

SELECT *
INTO tmpTable
FROM MyTable

when the table ("MyTable") is ready to have its data back use the INSERT statement. Following our example we have:

INSERT MyTable (Col1, Col2, Col3)
SELECT (Col1, Col2, Col3)
FROM tmpTable

Column lists are used here as omitting column lists may not be supported on all servers. Once the table ("MyTable") gets its data back, we would then DROP "tmpTable."

mod date: 2000-12-24T22:57:04.000Z