HomeDatabaseSQL Server: Copy One Table to Another Using Stored Procedure

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];

 

Vishwanath Dalvi
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.
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

LATEST REVIEWS

Recent Comments

error: Content is protected !!