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