sqlsql-server-2008t-sqlsql-server-2008-r2

SQL Server - Create a copy of a database table and place it in the same database?

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.

Solution

  • Use 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;