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
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