Friday, October 2, 2015

HOW TO INSERT UNIQUELY IDENTIFIED COLUMNS DATA INTO ANOTHER TABLE

SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Data_Conversion_History')
    CREATE TABLE CRM_Staging_Data.dbo.Data_Conversion_History(
        [data_conversion_history_id] [int] IDENTITY(1,1) NOT NULL,
        [script_name] [nvarchar](1024) NULL,
        [run_date] [datetime] NULL
    ) 

IF EXISTS ( SELECT * FROM CRM_Staging_Data.dbo.Data_Conversion_History WITH(NOLOCK) WHERE script_name = '01_15-CLM-002_Data_Script' )
        RETURN;
BEGIN TRY
DECLARE @min_rowid UNIQUEIDENTIFIER
DECLARE @max_rowid UNIQUEIDENTIFIER
DECLARE @last_eas_contact_policy_relationshipBase_copy_log_id INT

SELECT 
    @max_rowid = MAX(eas_policycontactId) 
    ,@min_rowid = MIN(eas_policycontactId) 
FROM 
    dbo.eas_policycontactBase WITH(NOLOCK)

IF EXISTS (SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='eas_contact_policy_relationshipBase_copy_log')         
BEGIN      
    DROP TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
END

CREATE TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
    (        
        eas_contact_policy_relationshipBase_copy_log_id BIGINT IDENTITY(1,1), 
        eas_policycontactId  UNIQUEIDENTIFIER,
        CONSTRAINT PK_SRC_Policy_relationbase PRIMARY KEY CLUSTERED (eas_contact_policy_relationshipBase_copy_log_id)        
    )  

--Insert the first record. Others will use the while block
MERGE dbo.eas_contact_policy_relationshipBase AS target
USING (
        SELECT
            NEWID() AS eas_contact_policy_relationshipId
            ,eas_ContactId
            ,eas_PolicyNumberId
            ,eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase WITH(NOLOCK)
        WHERE
            eas_policycontactId = @min_rowid
        AND     eas_ContactId IS NOT NULL 
        ) AS source
ON ( 1=0 )
WHEN NOT MATCHED THEN
INSERT VALUES (    source.eas_contact_policy_relationshipId,NULL,source.eas_ContactId,source.eas_PolicyNumberId )
OUTPUT
    source.eas_policycontactId
INTO
    CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log ( eas_policycontactId );

WHILE ( @min_rowid <= @max_rowid )
BEGIN
    ;WITH [UpdateList] AS (
        SELECT TOP (100000) 
            sel.eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase sel WITH(NOLOCK) 
        WHERE
            sel.eas_policycontactId > @min_rowid
        AND     sel.eas_ContactId IS NOT NULL 
        ORDER BY
            sel.eas_policycontactId ASC
    )
    MERGE dbo.eas_contact_policy_relationshipBase AS target
    USING (
        SELECT
            NEWID() AS eas_contact_policy_relationshipId
            ,eas_ContactId
            ,eas_PolicyNumberId
            ,main.eas_policycontactId
        FROM
            dbo.eas_PolicyContactBase main  WITH(NOLOCK)
        INNER JOIN
            [UpdateList] list
            ON ( main.eas_policycontactId = list.eas_policycontactId )
        ) AS source
    ON ( 1=0 )
    WHEN NOT MATCHED THEN
    INSERT VALUES (    source.eas_contact_policy_relationshipId,NULL,source.eas_ContactId,source.eas_PolicyNumberId )
    OUTPUT
        source.eas_policycontactId
    INTO
        CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log ( eas_policycontactId );
       
   
    SELECT @last_eas_contact_policy_relationshipBase_copy_log_id = IDENT_CURRENT('CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log')
       
    SET @min_rowid = ( SELECT eas_policycontactId FROM CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log WITH(NOLOCK) 
                        WHERE eas_contact_policy_relationshipBase_copy_log_id = @last_eas_contact_policy_relationshipBase_copy_log_id )
       
    IF ( @min_rowid = @max_rowid )
        BREAK;
END

INSERT INTO CRM_Staging_Data.dbo.Data_Conversion_history (script_name,run_date)
VALUES('01_15-CLM-002_Data_Script',GETDATE())   

IF EXISTS (SELECT * FROM CRM_Staging_Data.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='eas_contact_policy_relationshipBase_copy_log')         
BEGIN      
    DROP TABLE CRM_Staging_Data.dbo.eas_contact_policy_relationshipBase_copy_log        
END

END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

    RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH


No comments:

Post a Comment