Wednesday, April 14, 2010

SQL Server procedure - Backup table data


Script :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupTable]
@SchemaName nvarchar(255),
@TableName nvarchar(255)
AS

Set @SchemaName = replace(replace(@SchemaName,'[',''),']','')
Set @TableName = replace(replace(@TableName,'[',''),']','')

declare @Tstamp nvarchar(max) = Replace(Replace(Replace(convert(varchar(25), getdate(), 120),'-',''),' ','_'),':','')
declare @InputTable nvarchar(max)= '['+@SchemaName+'].['+@TableName+']'
declare @OutputTable nvarchar(max)= '[bckup].['+@TableName+'_'+@Tstamp+']'


declare @lsql nvarchar(max) = '
Select * into '+@OutputTable+' from '+@InputTable+''

EXEC(@lsql)

No comments:

Post a Comment