Hey buddy, I tried this and mostly +ve that it should be working, but if you find any flaws in it please point me out, related to anything in this Code.
ALTER procedure [EOC].[UpdateFalloutSolicitationResponseErrorDetail] (@FullFileName nvarchar(max)='')
AS
BEGIN
TRUNCATE TABLE Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
TRUNCATE TABLE Saw_raw.[EOC].[WorkInserted]
DECLARE @TeamId bigint =0, @MaxInstanceIdFromSaw_2 bigint=0, @ActivityId bigint=0
, @BatchId bigint=0, @StatusId int=0, @CreatedbyAccountId int=0, @DepartmentId int=1, @PieceCount int=1, @RecordCount int =0;
SELECT top 1 @TeamId=Id FROM SAW_2.[dbo].[Team] WHERE TeamName='MEP - Small Group' AND DepartmentId=@DepartmentId
SELECT top 1 @ActivityId=Id FROM SAW_2.[dbo].Activity WHERE ActivityName='EoC – Facets Inbound Error Report' AND TeamId=@TeamId
SELECT top 1 @BatchId=Id FROM SAW_2.[dbo].Batch WHERE BatchName ='EoC Fallout Error Reports'
SELECT top 1 @CreatedbyAccountId=Id FROM SAW_2.[dbo].Account WHERE LanId ='SYSTEM'
select @StatusId=Id from SAW_2.[dbo].[Status] WHERE StatusName='New'
IF EXISTS (
SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
)
BEGIN<br />
ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]
DROP CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_LastName ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
IF EXISTS (SELECT name FROM sys.indexes<br />
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
BEGIN
DROP INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
END
MERGE INTO
EOC.FalloutSolicitationResponseErrorDetail a
USING
SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] b
ON
--a.InstanceId = b.Id
LTRIM(RTRIM(ISNULL(a.CoveredID, '')))=LTRIM(RTRIM(ISNULL(b.[0 Covered ID],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredindividualsInsuranceIDorCardnumber, '')))=LTRIM(RTRIM(ISNULL(b.[1 Covered individuals Insurance ID/card number],'')))
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[2 User Defined field 2],'')))
AND LTRIM(RTRIM(ISNULL(a.SSNorITIN, '')))=LTRIM(RTRIM(ISNULL(b.[3 SSN or ITIN],'')))
AND LTRIM(RTRIM(ISNULL(a.FirstName, '')))=LTRIM(RTRIM(ISNULL(b.[4 First Name],'')))
AND LTRIM(RTRIM(ISNULL(a.MiddleName, '')))=LTRIM(RTRIM(ISNULL(b.[5 Middle Name],'')))
AND LTRIM(RTRIM(ISNULL(a.LastName, '')))=LTRIM(RTRIM(ISNULL(b.[6 Last Name],'')))
AND LTRIM(RTRIM(ISNULL(a.Suffix, '')))=LTRIM(RTRIM(ISNULL(b.[7 Suffix],'')))
AND convert(nvarchar(MAX), a.DateofBirth, 110) = convert(nvarchar(MAX), Cast(b.[8 Date of Birth] as Datetime), 110)
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[9 User Defined field 1],'')))
AND LTRIM(RTRIM(ISNULL(a.UserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[10 User Defined field 3],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[11 Covered User Defined field 1],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[12 Covered User Defined field 2],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[13 Covered User Defined field 3],'')))
AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField4, '')))=LTRIM(RTRIM(ISNULL(b.[14 Covered User Defined field 4],'')))
AND LTRIM(RTRIM(ISNULL(a.Comments, '')))=LTRIM(RTRIM(ISNULL(b.[15 Comments],'')))
AND LTRIM(RTRIM(ISNULL(a.SolicitationRequiredIndicator, '')))=LTRIM(RTRIM(ISNULL(b.[16 Solicitation Required indicator],'')))
AND LTRIM(RTRIM(ISNULL(a.ErrorMessage, '')))=LTRIM(RTRIM(ISNULL(b.[17 Error Message],'')))
WHEN MATCHED THEN
UPDATE SET
a.CoveredID=b.[0 Covered ID]
,a.CoveredindividualsInsuranceIDorCardnumber=b.[1 Covered individuals Insurance ID/card number]
,a.UserDefinedField2=b.[2 User Defined field 2]
,a.SSNorITIN=b.[3 SSN or ITIN]
,a.FirstName=b.[4 First Name]
,a.MiddleName=b.[5 Middle Name]
,a.LastName=b.[6 Last Name]
,a.Suffix=b.[7 Suffix]
,a.DateofBirth=b.[8 Date of Birth]
,a.UserDefinedField1=b.[9 User Defined field 1]
,a.UserDefinedField3=b.[10 User Defined field 3]
,a.CoveredUserDefinedField1=b.[11 Covered User Defined field 1]
,a.CoveredUserDefinedField2=b.[12 Covered User Defined field 2]
,a.CoveredUserDefinedField3=b.[13 Covered User Defined field 3]
,a.CoveredUserDefinedField4=b.[14 Covered User Defined field 4]
,a.Comments=b.[15 Comments]
,a.SolicitationRequiredIndicator=b.[16 Solicitation Required indicator]
,a.ErrorMessage=b.[17 Error Message]
,a.ModifiedDate=getdate()
,a.ModifiedBy=user_name()
WHEN NOT MATCHED THEN
INSERT
(
--InstanceId,
CoveredID
,CoveredindividualsInsuranceIDorCardnumber
,UserDefinedField2
,SSNorITIN
,FirstName
,MiddleName
,LastName
,Suffix
,DateofBirth
,UserDefinedField1
,UserDefinedField3
,CoveredUserDefinedField1
,CoveredUserDefinedField2
,CoveredUserDefinedField3
,CoveredUserDefinedField4
,Comments
,SolicitationRequiredIndicator
,ErrorMessage
,CreatedDate
,CreatedBy
,ModifiedDate
,ModifiedBy
)
VALUES
(
--b.[Id],
b.[0 Covered ID]
,b.[1 Covered individuals Insurance ID/card number]
,b.[2 User Defined field 2]
,b.[3 SSN or ITIN]
,b.[4 First Name]
,b.[5 Middle Name]
,b.[6 Last Name]
,b.[7 Suffix]
,b.[8 Date of Birth]
,b.[9 User Defined field 1]
,b.[10 User Defined field 3]
,b.[11 Covered User Defined field 1]
,b.[12 Covered User Defined field 2]
,b.[13 Covered User Defined field 3]
,b.[14 Covered User Defined field 4]
,b.[15 Comments]
,b.[16 Solicitation Required indicator]
,b.[17 Error Message]
,GetDate()
,CURRENT_USER
,GetDate()
,CURRENT_USER
)
OUTPUT
Inserted.InstanceId
,Inserted.CoveredID
,Inserted.CoveredindividualsInsuranceIDorCardnumber
,Inserted.UserDefinedField2
,Inserted.SSNorITIN
,Inserted.FirstName
,Inserted.MiddleName
,Inserted.LastName
,Inserted.Suffix
,Inserted.DateofBirth
,Inserted.UserDefinedField1
,Inserted.UserDefinedField3
,Inserted.CoveredUserDefinedField1
,Inserted.CoveredUserDefinedField2
,Inserted.CoveredUserDefinedField3
,Inserted.CoveredUserDefinedField4
,Inserted.Comments
,Inserted.SolicitationRequiredIndicator
,Inserted.ErrorMessage
,Inserted.CreatedDate
,Inserted.CreatedBy
,Inserted.ModifiedDate
,Inserted.ModifiedBy
INTO
SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
(
InstanceId
,CoveredID
,CoveredindividualsInsuranceIDorCardnumber
,UserDefinedField2
,SSNorITIN
,FirstName
,MiddleName
,LastName
,Suffix
,DateofBirth
,UserDefinedField1
,UserDefinedField3
,CoveredUserDefinedField1
,CoveredUserDefinedField2
,CoveredUserDefinedField3
,CoveredUserDefinedField4
,Comments
,SolicitationRequiredIndicator
,ErrorMessage
,CreatedDate
,CreatedBy
,ModifiedDate
,ModifiedBy
);
INSERT INTO Saw_2.[dbo].[Work]
(<br />
[BatchId]
,[StatusId]
,[ActivityId]
,[CreatedbyAccountId]<br />
,[PieceCount]<br />
,[BscReceiptDateTs]
,[WorkflowCreatedTs]<br />
,[InstanceId]<br />
)
OUTPUT
inserted.Id
, inserted.BatchId
,inserted.StatusId
,inserted.ActivityId
,inserted.CreatedbyAccountId<br />
,inserted.PieceCount<br />
,inserted.BscReceiptDateTs
,inserted.WorkflowCreatedTs<br />
,inserted.InstanceId
INTO Saw_raw.[EOC].[EOCWorkInserted]
(
Id
, BatchId
,StatusId
,ActivityId
,CreatedbyAccountId<br />
,PieceCount<br />
,BscReceiptDateTs
,WorkflowCreatedTs<br />
,InstanceId
)
SELECT @BatchId, @StatusId, @ActivityId, @CreatedbyAccountId, @PieceCount,getdate() ,getdate(), InstanceId
FROM SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
--FROM SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] WHERE Id NOT IN
--(
-- SELECT InstanceId FROM Saw_2.[dbo].[Work] WHERE ActivityId=@ActivityId
--);
SELECT @RecordCount=Count(*) FROM Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
UPDATE [Saw_2].[dbo].[FileLoad] SET [RecordCount]=@RecordCount
WHERE rtrim(ltrim([FullFileName]))=rtrim(ltrim(@FullFileName))
IF NOT EXISTS (
SELECT * FROM sys.key_constraints
WHERE type = 'PK'
AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
)
BEGIN
ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]
ADD CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId PRIMARY KEY CLUSTERED (InstanceId);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID
ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredID);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber
ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredindividualsInsuranceIDorCardnumber);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN
ON [EOC].[FalloutSolicitationResponseErrorDetail](SSNorITIN);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_LastName
ON [EOC].[FalloutSolicitationResponseErrorDetail](LastName);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth
ON [EOC].[FalloutSolicitationResponseErrorDetail](DateofBirth);
END
IF NOT EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
BEGIN
CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive
ON [EOC].[FalloutSolicitationResponseErrorDetail](IsActive);
END
END
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|