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