T-SQL copy and clone an existing table.

Something really useful that I like in Microsoft Access, is the capacity to select and clone an object just with a menu command.

The options we have in access are:

image

Structure only – will copy/paste into a new table the table structure, so the index, the primary key and so on.

Structure and Data – will copy/paste everything inside a new table.

Append – will copy the data into the existing cloned object.

What can we do with SQL Server?

Of course with SQL Server we cannot simply do that, especially if we are considering to automate this process inside a script or better, inside a job.

The first idea is to use the simple script SELECT INTO in this way:

  1: IF  EXISTS 
  2:    (SELECT * 
  3:    FROM sys.objects 
  4:    WHERE object_id = OBJECT_ID(N'[schema].[tbl_pippo]') 
  5:    AND type in (N'U'))
  6: DROP TABLE [schema].[tbl_pippo]
  7: GO
  8: 
  9: SELECT * 
 10: INTO [schema].[tbl_pippo]
 11: FROM [schema].[tbl_pluto]

In this way we will copy: the records, the table structure but not the index or the primary keys.

We can try to use the right click command Drop and Create and then change the destination name of our table:

New Picture (9)

Finally, we can use the SSIS service and create a powerful package able to do that. In this way we can be sure we are going to clone everything and decide if we want to copy/paste also the data.

Transfer SQL Server object task. This task will do everything we need to copy and paste an object inside SQL:

image

We can also use some variables in order to make our task dynamic.

Pretty cool, isn’t it?

Tags: