Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1388

Re: Purge Utility

Show all headers | View raw

Below is the full process.

Basically a procress to clean-up some staging tables for a conversion.

SImply SQL works fine but I am having a big problem with data with apostrophes.

IF OBJECT_ID ( 'dbo.PurgeUtility', 'U' ) IS NOT NULL 
   DROP TABLE dbo.PurgeUtility;

Create table PurgeUtility (
TableName   varchar(128) NOT NULL, 
PurgeRange  varchar(2000) NOT NULL 
);

--
--
--

alter table PurgeUtility  add constraint PK_PurgeUtility Primary KEY (TableName);


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [TableName]
      ,[PurgeRange]
  FROM [CISStagingDB].[dbo].[PurgeUtility]
  
  
DROP TABLE REP_BUS_DIR_NO_INSTMT_JAW  
GO
SELECT  * INTO REP_BUS_DIR_NO_INSTMT_JAW FROM QUERY WITH (NOLOCK)  
GO

delete from PurgeUtility where TableName = 'BUS_DIR_NO_INSTMT_JAW'

INSERT INTO [CISStagingDB].[dbo].[PurgeUtility]([TableName],[PurgeRange])
VALUES ('BUS_DIR_NO_INSTMT_JAW','KY_BA IN (SELECT KY_BA  FROM  [CISStagingDB].[dbo].[REP_BILL_ACCT] (nolock) WHERE  CD_OFFICE > '  + '''079''' + '  OR  CD_ACCT_TYPE > 55)');


--
--  Example  insert for each file you wish to proces


The Full code

Below is the entire code:

USE CISStagingDB;

IF OBJECT_ID ( 'dbo.usp_PurgeCISSTAGEDB', 'P' ) IS NOT NULL 
   DROP PROCEDURE dbo.usp_PurgeCISSTAGEDB;
GO

CREATE Procedure dbo.usp_PurgeCISSTAGEDB
( @I_TABLE varchar(128),@I_MODE varchar(10) = 'SIMULATE')
AS
/*

       
*/
BEGIN
SET NOCOUNT ON;

DECLARE @VSQLQuery            NVARCHAR(2000),
        @VCount               INT,
        @ErrorMessage         NVARCHAR(4000),
        @VParameterDefinition NVARCHAR(100);

/*  
 BEGIN TRANSACTION;
 
*/

  
    	PRINT '===================================================='	
		PRINT 'Start time=' + cast(getdate() as varchar)
		PRINT '===================================================='	

			-- Check for Table Criteria
	    Select  @VParameterDefinition = PurgeRange
         from PurgeUtility where Tablename = @I_TABLE
		 
		 If @@ROWCOUNT <> 1
		 BEGIN
		  PRINT @I_TABLE + ' Table not in the PurgeUtility table'
		  RETURN 4
		 END
	    -- Clean up old TEMP_TABLE_NAME if it exists
	
		  SET   @VSQLQuery = ' IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID(''TEMP_'+@I_TABLE+''') AND type in (''U''))  
          BEGIN  
            DROP TABLE TEMP_' + @I_TABLE +   
           ' END'
        PRINT 'Clean-up Processing Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)     

		PRINT '===================================================='	
        PRINT @VSQLQuery
		PRINT '===================================================='
		IF @I_MODE = 'SIMULATE'
		 BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE	
         EXECUTE sp_executesql @VSQLQuery;
 	  	
		-- Create  Staging Table
	    SET   @VSQLQuery = 'TEMP_' + @I_TABLE 
		PRINT 'Create Staging Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'SELECT * INTO ' + @VSQLQuery + ' FROM REP_' + @I_TABLE + ' WHERE 0=1'
        PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
         BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
	     EXECUTE sp_executesql @VSQLQuery;
 	    	    	
		--   Backup the critera of rows (TABLOCK required for MINIMAL LOG) TEMP_TABLE
		SET   @VSQLQuery = 'TEMP_' + @I_TABLE 
		PRINT 'Fill Staging Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM REP_' + @I_TABLE
		PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
		  BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
		 BEGIN
		  EXECUTE sp_executesql @VSQLQuery;
		  SELECT @Vcount = @@ROWCOUNT
          PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		  PRINT '===================================================='	
 	    
 	      IF @Vcount = 0
		   BEGIN
		    PRINT 'REP_' + @I_TABLE + ' is empty'
		    PRINT '===================================================='	
		    RETURN 4
	      END
 	    END
		--   Truncate  REP_table
		SET   @VSQLQuery = 'REP_' + @I_TABLE 
		PRINT 'TRUNCATE REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
        SET   @VSQLQuery = 'TRUNCATE TABLE ' + @VSQLQuery 
		PRINT '===================================================='	
		PRINT @VSQLQuery
		PRINT '===================================================='	
        IF @I_MODE = 'SIMULATE'
		  BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
		ELSE
		 EXECUTE sp_executesql @VSQLQuery; 
			
	    BEGIN TRY
		 -- Load the data critera of rows (TABLOCK required for MINIMAL LOG) REP_TABLE
		 SET   @VSQLQuery = 'REP_' + @I_TABLE 
		 PRINT 'Fill REP Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)    
         SET   @VSQLQuery = 'INSERT INTO ' + @VSQLQuery + ' WITH (TABLOCK) SELECT * FROM TEMP_' + @I_TABLE+ ' WHERE ' + @VParameterDefinition
		 PRINT '===================================================='	
		 PRINT @VSQLQuery
		 PRINT '===================================================='	
         IF @I_MODE = 'SIMULATE'
		  BEGIN
		   PRINT 'Simulation mode this step is skipped'
		   PRINT '===================================================='
          END
		 ELSE
		  BEGIN
		   EXECUTE (@VSQLQuery)
 	       SELECT @Vcount = @@ROWCOUNT
		   PRINT 'Rows Loaded to ' + @VSQLQuery + ' = ' + CAST(@Vcount AS VARCHAR(10))  
		   PRINT '===================================================='	
	     END
		End TRY
		BEGIN CATCH
        SELECT 
         @ErrorMessage = ERROR_MESSAGE();
        RAISERROR (@ErrorMessage, 16, 1);
        RETURN 4
        END CATCH
		-- Clean-up		
        SET   @VSQLQuery = ' IF  EXISTS (SELECT * FROM sys.objects 
          WHERE object_id = OBJECT_ID(''TEMP_'+@I_TABLE+''') AND type in (''U''))  
          BEGIN  
            DROP TABLE TEMP_' + @I_TABLE +   
           ' END'
        PRINT 'Clean-up Processing Table=' + @VSQLQuery + ' Time='  + cast(getdate() as varchar)     

		PRINT '===================================================='	
        PRINT @VSQLQuery
		PRINT '===================================================='	
       IF @I_MODE = 'SIMULATE'
		 BEGIN
		  PRINT 'Simulation mode this step is skipped'
		  PRINT '===================================================='
         END
	   ELSE
	    EXECUTE sp_executesql @VSQLQuery;
	   PRINT 'End  time=' + cast(getdate() as varchar)
       PRINT '===================================================='	
		
		
/*
     COMMIT TRANSACTION;
  END TRY

  BEGIN CATCH

     IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION;
      END
      EXECUTE [dbo].[usp_LogError];  
      RAISERROR ('Review Error in the load CISDB process',       -- Message text.
                  16,                                            -- Severity.
                  1                                              -- State.
                 );
                    /
  END CATCH;
  */
END;

GO

-- Run in simulation mode to verify your SQL
-- exec dbo.usp_PurgeCISSTAGEDB 'SAD'
-- Run for real
-- exec dbo.usp_PurgeCISSTAGEDB 'SAD','EXECUTE'

--select count(*) from TEMPX_SAD where KY_BA = 0

-- insert into REP_SAD select * from TEMPX_SAD

exec dbo.usp_PurgeCISSTAGEDB 'BUS_DIR_NO_INSTMT_JAW','EXECUTE'

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-13 12:44 -0800
  Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-13 22:25 +0100
  Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:10 -0800
    Re: Purge Utility Erland Sommarskog <esquel@sommarskog.se> - 2013-02-14 21:00 +0100
  Re: Purge Utility JAW <jwilliam@aglresources.com> - 2013-02-14 08:22 -0800
  Re: Purge Utility JAW <willjamu@gmail.com> - 2013-02-21 17:38 -0800
    Re: Purge Utility rja.carnegie@gmail.com - 2013-02-21 18:04 -0800

csiph-web