Thursday, January 14, 2016

Copy a table into new table with and without data - SQL Server

This article explains  how to copy an existing table to new table in SQL Server.
Lets see how to copy an existing table to new table in SQL Server. There are two options. They are
  • Copy only the structure of an existing table into new table
  • Copy only the structure with data of an existing table into new table

Copy only the structure of an existing table into new table:

SELECT * INTO tblNew FROM tblOld WHERE 1=2


The above query will copy the structure of  an existing table(tblOld) into the new table(tblNew).

Copy only the structure with data of an existing table into new table:

SELECT * INTO tblNew FROM tblOld

This is also same like the previous query, but it copies the structure of existing table(tblOld) with data as well into the new table(tblNew).

Note:-  This will not copy indexes, keys, etc.

If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.

If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.

0 comments:

Post a Comment