SQL Table Copy Stored Procedure
Posted by Joe Jacob, Senior Developer on February 26, 2017
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.
- 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.
- 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.