SQL Table Copy SP

More by

Do you ever get tired of typing ‘Select * INTO SourceTable02417 FROM SourceTable’ to make a quick backup of a SQL Sever table?

I did, so here’s a quick and dirty little stored procedure to take care of that manual process.

Here’s how it works:

When you call this Procedure (PROC), simply use the following syntax.

XTABLECOPY 'SOURCETABLENAME', 1

You sill get a SELECT * INTO type of table copy with a  unique talbe name, based on the current date and time, and a little validation.

Parameters:

  • The first parameter is the table that you are copying from.
  • The second is to tell the PROC if you want to copy the table into an external database which you hard code into the PROC.  In my test environment and at some client sites I target a catch all database to make ADHOC table backups so that production remains clean.

Features:

  • A pre and post count of records will be tallied and displayed after the copy to give some assurances that the copy was successful.
  • A prefix will be added to the name of the destination table name.
  • A suffix will be added to the destination table name in a date/time format to provide uniqueness and to tell me exactly when the copy occurred.
  • As mentioned above you also have an option of copying to the current database (value 0 in PARM2) or to a predefined database (value 1 in PARM2.)

Example & Download Code:

Here is the code for you to copy and paste:

create Proc [dbo].xTableCopy 
   @table varchar (50), 
   @useExternalDB smallint 
as 
Set NoCount on 
-- constants 
-- in case you want your proc to always use an external database for copying (this is controlled with a 1 in the 2nd parm) 
declare @externalDatabaseNameDefault as varchar(100) = 'CrestwoodSupportDB.dbo.' 

-- leave blank if you do not want a prefix for the destination table. 
declare @destinationPrefix as varchar(10) = 'x_' 

-- declares 
declare @stmt varchar(200) 
declare @verifyCount int 
declare @verifyCountAfterCopy int 
declare @ExternalDatabaseName varchar(60) 
declare @timeString varchar(50) = replace( convert(varchar(10),getdate(), 102),'.','') + '_' + left( replace( convert(time,getdate(),100),':',''),6) 
declare @destinationTable varchar(200) 

-- copy to external database or keep store in current database 
if (@useExternalDB = 1) 
   set @ExternalDatabaseName = @externalDatabaseNameDefault 
else ​ 
   set @ExternalDatabaseName = '' 

-- create a temp table to store some validation numbers. 
create table #xValidateInfo (recCount int) 

-- what is our current record count? store it. 
set @stmt = 'insert into #xValidateInfo select count(*) from ' +@table 
execute(@stmt) 
select top 1 @verifyCount = recCount from #xValidateInfo 
delete from #xValidateInfo 

-- build is our destination table name string 
set @destinationTable = @ExternalDatabaseName + 'x_' + @table + @timeString 

-- do the actual copy and display the command 
set @stmt = 'select * into ' + @destinationTable + ' from ' + @table + ' (nolock) ' 
print @stmt 
execute(@stmt) 

-- count the destination table 
set @stmt = 'insert into #xValidateInfo 
select count(*) from ' +@destinationTable 
execute(@stmt) 
select top 1 @verifyCountAfterCopy = recCount from #xValidateInfo 
print 'Source record Count = ' + cast(@verifyCount as varchar(10)) 
print 'Destination record Count = ' + cast(@verifyCountAfterCopy as varchar(10)) 
if (@verifyCount = @verifyCountAfterCopy) ​ 
print 'Successful !' 
else ​ 
print 'Copy FAILED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' 

-- don't need this anymore 
drop table #xValidateInfo

If you need some assistance, email us at Support@Crestwood.com and we can help you out.

Leave a Reply