SQL Script Demo

USE Newegg
Go
/*================================================================================  
Server:    Newsql  
DataBase:  Newegg
Author:    Benny
Object:    [dbo].[UP_NESO_TransferCOMReasonCodeLog]
Date:      2009-10-08
Content:   transfer com reasoncode log to history table macthing following conditions
    (1) SO Status='C'
    (2) all suggestion has approved
    (3) has allocation log
    (4) if Voided order ,tranfer directly
================================================================================*/ 
CREATE PROCEDURE [dbo].[UP_NESO_TransferCOMReasonCodeLog] 
	@batchCount   INT
AS
BEGIN 
	SET NOCOUNT ON

    DECLARE @rows INT,
    @rows_limit INT,
    @row_batch INT,
    @row_count INT;

    SELECT @rows = 0,
        @rows_limit = 20000000, 
        @row_batch = @batchCount,       
        @row_count = @row_batch;

    --(1)create temp table 
    IF OBJECT_ID(N'tempdb.dbo.#Temp_NESO_COMReasonLog', N'U') IS NOT NULL 
            DROP TABLE #Temp_NESO_COMReasonLog
    CREATE TABLE #Temp_NESO_COMReasonLog(
            TransactionNumber INT,
            SONumber INT  NOT NULL,
            CustomerNumber INT  NOT NULL,
            CompanyCode INT,
            ItemNumber NCHAR(25) NOT NULL,
            RealItemNumber NCHAR(25),
            SuggestionNumber INT,
            SeqNumber NCHAR(2),
            SOStatus  NCHAR(1),
            ReasonCode NCHAR(50),
            ReasonDescription NVARCHAR(500),
            GLCode NCHAR(30),
            ReferenceNumber NCHAR(20),
            Description NVARCHAR(100),
            UnitPrice DECIMAL(10, 2),
            Quantity INT,
            ExtendPrice DECIMAL(10, 2),
            IsShippingAdjust NCHAR(1),
            IsItemOrOrder NCHAR(1),
            ShippingCharge DECIMAL(10, 2),
            CurrencyExchangeRate DECIMAL(12,6),
            CurrencyCode NCHAR(5),
            CurrencyUnitPrice DECIMAL(10, 2),
            CurrencyExtendPrice DECIMAL(10, 2),
            CurrencyShippingCharge DECIMAL(10, 2), 
            LanguageCode NCHAR(5)
    )  
    
    WHILE (@row_count = @row_batch AND @rows < @rows_limit)
    BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
        --(1)truncata temp table
        TRUNCATE TABLE #Temp_NESO_COMReasonLog
        
        --(2)delete data from current table and output deleted table into temp table
        DELETE TOP (@row_batch) FROM [dbo].[SO_COMReasonCode_Log] 
        OUTPUT DELETED.* INTO #Temp_NESO_COMReasonLog
        WHERE  (SONumber in
        ( 
             SELECT SONumber
             FROM dbo.SO_COMReasonCode_Log WITH(NOLOCK)  
             WHERE SOStatus = 'C'  --close SO
             GROUP BY SONumber 
             HAVING SUM(
             	    CASE WHEN ReasonCode IS NULL OR ReasonCode =''  THEN 0 
             	        ELSE 1
             	    END
             ) = COUNT(SONumber)   --do have unapproved reason code
         )   
        AND EXISTS    --already allocated
        (     SELECT TOP 1 1   
              FROM dbo.SO_COMAllocation_LogMaster T1 WITH (NOLOCK)  
              WHERE T1.SoNumber = SONumber
        )
        AND EXISTS   --already allocated
       (      SELECT TOP 1 1   
              FROM dbo.SO_COMAllocation_LogTransaction T2 WITH (NOLOCK)  
              WHERE T2.SoNumber = SONumber
       )) 
       OR 
       (SOStatus ='V') --for void orders ,tranfer directly
       
       SELECT  @row_count = @@ROWCOUNT ,@rows = @rows + @row_count

        --(3)insert data to log table from temp table
        SET IDENTITY_INSERT [dbo].[SO_COMReasonCode_LogHistory] ON
        INSERT INTO [Newegg].[dbo].[SO_COMReasonCode_LogHistory]
               ([TransactionNumber]
               ,[SONumber]
               ,[CustomerNumber]
               ,[CompanyCode]
               ,[ItemNumber]
               ,[RealItemNumber]
               ,[SuggestionNumber]
               ,[SeqNumber]
               ,[SOStatus]
               ,[ReasonCode]
               ,[ReasonDescription]
               ,[GLCode]
               ,[ReferenceNumber]
               ,[Description]
               ,[UnitPrice]
               ,[Quantity]
               ,[ExtendPrice]
               ,[IsShippingAdjust]
               ,[IsItemOrOrder]
               ,[ShippingCharge]
               ,[CurrencyExchangeRate]
               ,[CurrencyCode]
               ,[CurrencyUnitPrice]
               ,[CurrencyExtendPrice]
               ,[CurrencyShippingCharge]
               ,[LanguageCode])
            SELECT [TransactionNumber]
                   ,[SONumber]
                   ,[CustomerNumber]
                   ,[CompanyCode]
                   ,[ItemNumber]
                   ,[RealItemNumber]
                   ,[SuggestionNumber]
                   ,[SeqNumber]
                   ,[SOStatus]
                   ,[ReasonCode]
                   ,[ReasonDescription]
                   ,[GLCode]
                   ,[ReferenceNumber]
                   ,[Description]
                   ,[UnitPrice]
                   ,[Quantity]
                   ,[ExtendPrice]
                   ,[IsShippingAdjust]
                   ,[IsItemOrOrder]
                   ,[ShippingCharge]
                   ,[CurrencyExchangeRate]
                   ,[CurrencyCode]
                   ,[CurrencyUnitPrice]
                   ,[CurrencyExtendPrice]
                   ,[CurrencyShippingCharge]
                   ,[LanguageCode]
            FROM  #Temp_NESO_COMReasonLog

            SET IDENTITY_INSERT [dbo].[SO_COMReasonCode_LogHistory] OFF

            --(4)trancate temp table
            TRUNCATE TABLE #Temp_NESO_COMReasonLog
            
            --(5)commit transaction
            COMMIT TRANSACTION
            
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION 
        END CATCH
        WAITFOR DELAY '00:00:10';   
    END  --end of while loop
    
    --PRINT 'rows transfered:' + cast(@rows AS char(20))
    
    IF OBJECT_ID(N'tempdb.dbo.#Temp_NESO_COMReasonLog', N'U') IS NOT NULL 
        DROP TABLE #Temp_NESO_COMReasonLog
END     --end of SP
GO