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.

Answer

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;