Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft - Microsoft SQL Server - SQL - Tables - Backup Table with Delete ======

<code sql>
CREATE PROCEDURE [dbo].[webCreateBackUp_xtCusDeDeup_SelectionAdd]
AS

/* Data Declarations */
DECLARE	@CMD  nvarchar(max),
		@ParmDefinition nvarchar(500),
		@TableSearch nvarchar(100),
		@Object nvarchar(50),
		@TableName sysname,
		@SourceDBName sysname,
		@TargetDBName sysname

/* Set initial variables */

SET @SourceDBName = 'dswMatchReview' -- Data is selected from here
SET @TargetDBName = 'dswMatchReview_Backup' -- Data is written to here
SET @Object = 'xtCusDedup_Selection'

/* Take Daily backup of xtCusDeDeup_Selection */
  
  BEGIN
      
      SET @CMD = '
				  SELECT * INTO ['+@TargetDBName+'].dbo.['+@Object+'_Backup_'
                  + replace(convert(nvarchar, getDate(), 23),'-','') + '_'
                  + replace(convert(nvarchar, getDate(), 24),':','')
                  + '] FROM ['+@SourceDBName+'].dbo.['+@Object+']
				 '
      
	  EXEC sp_executeSQL @CMD
  
  END		 

/* Drop the any tables that are older than 14 days - procedure will run daily so check for table that has timestamp from 15 days ago */

SET @TableSearch = @Object+'_Backup_' + replace(convert(nvarchar,DATEADD(day,-15,getdate()), 23),'-','')+'%' -- 15 day old database table

SET @ParmDefinition = N'@TableNameOUT nvarchar(max) OUTPUT'

SET @CMD = 'SELECT @TableNameOUT = [Name] FROM '+@TargetDBName+'.sys.tables where [Name] LIKE 
				   '''+@TableSearch+''''

EXEC sp_executeSQL @CMD, @ParmDefinition, @TableNameOUT=@TableName OUTPUT;

-- If the table is found, then it needs to be dropped (deleted)
IF @TableName IS NOT NULL

	BEGIN

	SET @CMD = '
				USE ['+@TargetDBName+'];
				EXEC(''
					   DROP TABLE dbo.['+@TableName+']
					   
					 '')
			   '

	EXECUTE sp_executeSQL @CMD

	END
</code>