I have a table ABC in a database DB. I want to create copies of ABC with names ABC_1, ABC_2, ABC_3 in the same DB. How can I do that using either Management Studio (preferably) or SQL queries ?
This is for SQL Server 2008 R2.
SELECT ... INTO:
SELECT * INTO ABC_1 FROM ABC;
This will create a new table
ABC_1 that has the same column structure as
ABC and contains the same data. Constraints (e.g. keys, default values), however, are -not- copied.
You can run this query multiple times with a different table name each time.
If you don't need to copy the data, only to create a new empty table with the same column structure, add a
WHERE clause with a falsy expression:
SELECT * INTO ABC_1 FROM ABC WHERE 1 <> 1;