SQL Server: Copy One Table to Another Using Stored Procedure

Posted October 23, 2012 by Vishwanath Dalvi in Database, SQL Server

When modifying an important table in the database, user frequently backup the table by making a copy of the original table with a different name. Using a stored procedure makes this process simple and convenient to reuse.

Data can be priceless. One of the ways to best protect is to duplicate the table. Let us walk through an example.

The syntax for a basic table copy command would be the following:

Select * 
Into   original_tablename_backup
From   original_tablename;


In our example, we will have a table named emp in our database. We wish to make a copy of the original emp table. This query will create an emp_backup table. It will raise an error if the emp_backup table already exists.

Select * 
into   emp_backup
from   emp;


As we need to perform these type of queries many times, we can use a stored procedure to make a copy of table. A stored procedure is nothing more than saved SQL that can be called repeatedly to perform similar functions.

@tbname AS varchar(MAX) 
SET nocount ON; 
DECLARE @query AS varchar(MAX)
SET @query = 'select * into '[email protected]+'_backup from '[email protected]+''; 
EXEC (@query)
SET nocount OFF; 


This stored procedure takes the original table name as a parameter and it will create original table name underscore backup table as a copy of original table.

Let’s see how to execute this ‘BACKUP_TB’ stored procedure with table name as parameter.

Suppose we have the dept table in our database, and we wish to make copy of dept table as name dept_backup.



We are executing our procedure ‘BACKUP_TB’ and passing the table name a parameter. Our stored procedure will create dept_backup table as a copy of original dept table.

Stored Procedure to copy one table to another.

If we have a different schema name in our database and we are making a copy of the table under AbcLtd schema, then we need to use brackets to parse the ‘.’ symbol as a parameter with our backup_tb procedure as the following:

EXEC BACKUP_TB [AbcLtd.salary];



About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.