Copy a Table

At times we need to copy a table, with all the data in it, from one SQL Server database to another. There several ways to do this:

1. Select Into
2. SSMS Import/Export Wizard
3. SSIS Transfer Object task

Select Into

select * into table1 from [server1].database1.schema1.table1

This statement creates an empty table with identical structure and populates it by copying data from the source table. Even if the table has an identity column it still works. We can specify a where clause to copy only part of the table.

Notes:

1. We may need to create a linked server if the source is on another SQL Server instance.
2. If the table already exists in the target database, you need to drop it first. You could add this into the script: “if exists (select * from sys.tables where name = ‘table1’) drop table table1”.
3. We could also script the table (right click the table name on object explorer and select “script table as”) and then do “insert into select from“. This way, if the target table has identity column: switch off identity insert, insert data (with column names), switch identity insert back on.

SSMS Import/Export Wizard

Right click the database name containing the table you want to copy, and select Task, Export data. On the Import Export Wizard specify the source database, target database and source table(s) that you want to copy. At the end of the wizard the table(s) will be copied across.

Notes:

1. We can specify the mapping between source and target columns.
2. We can copy just part of the table by specifying the where clause on the query.
3. We can save it as SSIS package for later run or scheduled run.
4. Destination table will be created. If it already exists, it will append the rows (not replace).

SSIS Transfer Object task

On BIDS create a new SSIS project. Create Transfer SQL Server Object task on the Control Flow. Double click the task and specify the source database, destination database, and the table(s) you want to copy. Also specify whether to drop the table if already exists, whether to copy the data, indexes, PK, FK, etc. Then press F5 to execute it (or right click on the task and select execute).

As always, there are pluses & minuses on each one: select into or script then insert is simple and quick but the work is manual. Using the Export wizard it’s more effortless, we can schedule it and we can copy more than 1 table, but can’t replace row/truncate first. Using SSIS we can copy several tables, and we can truncate/replace data but we can’t specify a query to copy partially.

As always, which one to use depends on our situation, see considerations above. But if I had to choose one for general situation, I would use SSIS Transfer Object task.

PS.

The term ‘copy a table’ is not the term that is used in database professional. In the database world, ‘copy’ is not a verb that is associated with a table. A table is created and dropped. We insert data into a table and delete rows from a table. But copy is not associated with a table. But, oddly enough, ‘copy a table’ is the term that people not from database background use. To them, a table can be copied. A table can be deleted (not dropped).

Ironically, Oracle, MySQL and DB2 people are more used to the term ‘copy a table’. Unlike in SQL Server world, they have a concept of copying a table. In Oracle, we have ‘create table table1 as select * from table2‘. In MySQL we have ‘create table table1 like table2’. In DB2 Control Center we have copy table window to create a copy of a table. In my opinion, in SQL Server, in Object Explorer, if we right click on a table, next to Rename and Delete we should see Copy. In the next version I mean (hint hint).