SQL Server: Copy One Table to Another Using Stored Procedure

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.

CREATE proc BACKUP_TB 
@tbname AS varchar(MAX) 
AS 
BEGIN
SET nocount ON; 
DECLARE @query AS varchar(MAX)
SET @query = 'select * into '+@tbname+'_backup from '+@tbname+''; 
EXEC (@query)
SET nocount OFF; 
END

 

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.

EXEC BACKUP_TB DEPT;

 

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.

  • Nik

    I just have a quick question. The first process of copying the database…can we do it on a live production sql database? I need to copy the table from live production sql people soft database. I do not want to take down the production database. Please tell me the step-by-step ways through which I can do that. Any help would be greatly appreciated!

    • Vishwanath Dalvi

      Do not perform any unplanned things directly on live production database.