Good Morning Everyone,
I am using Microsoft SQL Azure (RTM) - 12.0.2000.8.
I am trying to merge data from one table to another with the condition that if a record already exists it should update otherwise it should insert in the table.
For that I am using Merge.
Now I have an another requirement, in case I found a bad quality record (some record is missing some important column data ) it should insert in a different table. So that I could collect all bad quality records in a different place / table.
Below is my current store procedure.
BEGIN TRANSACTION;
MERGE HRSAP_DATA AS HSO
USING HR_SAP AS HS
ON TRY_CAST(HSO.SAPno AS BIGINT) = CAST(HS.SAP_No AS BIGINT)
-- INSERT NEW RECORDS--
WHEN NOT MATCHED BY TARGET--0 row effected
THEN
INSERT(vcFirstName,
vcLastName,
vcEmail,
vcMobile,
vcUserName,
vcprojectnumber,
SAPno,
dateofjoining,
dateofend,
_joinyear,
_endyear,
_joinminus,
_endminus,
CountryID,
telephoneno,
salutation,
Gender,
emptype,
chAdminType,
ContractPercentage,
CostCenter)
VALUES
(HS.first_name,
HS.last_name,
HS.email,
HS.mobile_no,
SUBSTRING(HS.email, 0, CHARINDEX('@', HS.email)),
-- HS.project_no,
(
SELECT CASE
WHEN HS.project_no <> ''
THEN SUBSTRING(HS.project_no, 2, LEN(HS.project_no) - 11) + '.' + SUBSTRING(HS.project_no, 4, LEN(HS.project_no) - 9) + '.' + SUBSTRING(HS.project_no, 8, LEN(HS.project_no) - 12) + '-' + SUBSTRING(HS.project_no, 9, LEN(HS.project_no) - 10) + '.' + SUBSTRING(HS.project_no, 12, LEN(HS.project_no) - 11)
ELSE HS.cost_center
END
), -- select * from HR_SAP),
CAST(HS.sap_no AS BIGINT),
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_joining AS DATE)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_leaving AS DATE)
END,
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021'
ELSE DATEPART(yy, HS.date_of_joining)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021'
ELSE DATEPART(yy, HS.date_of_leaving)
END,
ISNULL(dbo.CalculateLeaveMinus
(HS.country, HS.designation,
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_joining AS DATE)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_leaving AS DATE)
END, 1
), 0),
ISNULL(dbo.CalculateLeaveMinus
(HS.country, HS.designation,
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_joining AS DATE)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_leaving AS DATE)
END, 2
), 0),
ISNULL(
(
SELECT bintid_pk
FROM country_master
WHERE country_code = HS.countrycode
), 777),
HS.telephone_no,
HS.salutation,
UPPER(LEFT(HS.gender, 1)) + +LOWER(SUBSTRING(gender, 2, LEN(HS.gender))),
'NP',
'v',
HS.contract_percentage,
HS.cost_center
)
--START : UPDATE EXISTING RECORDS--
WHEN MATCHED
THEN UPDATE SET
HSO.btStatus = 1,
HSO.vcFirstName = HS.first_name,
HSO.vcLastName = HS.last_name,
HSO.vcEmail = HS.email,
HSO.vcMobile = HS.mobile_no,
HSO.CostCenter = HS.cost_center,
HSO.ContractPercentage = HS.contract_percentage,
HSO.vcUserName = SUBSTRING(HS.email, 0, CHARINDEX('@', HS.email)),
HSO.vcprojectnumber =
(
SELECT CASE
WHEN HS.project_no <> ''
THEN SUBSTRING(HS.project_no, 2, LEN(HS.project_no) - 11) + '.' + SUBSTRING(HS.project_no, 4, LEN(HS.project_no) - 9) + '.' + SUBSTRING(HS.project_no, 8, LEN(HS.project_no) - 12) + '-' + SUBSTRING(HS.project_no, 9, LEN(HS.project_no) - 10) + '.' + SUBSTRING(HS.project_no, 12, LEN(HS.project_no) - 11)
ELSE HS.cost_center
END
), --UPDATE WITH DOT
HSO.SAPno = CAST(HS.sap_no AS BIGINT),
HSO.dateofjoining = CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-08-11'
ELSE CAST(HS.date_of_joining AS DATE)
END,
HSO.dateofend = CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-08-11'
ELSE CAST(HS.date_of_leaving AS DATE)
END,
HSO._joinyear = CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021'
ELSE DATEPART(yy, HS.date_of_joining)
END,
HSO._endyear = CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021'
ELSE DATEPART(yy, HS.date_of_leaving)
END,
HSO._joinminus = ISNULL(dbo.CalculateLeaveMinus
(HS.country, HS.designation,
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_joining AS DATE)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_leaving AS DATE)
END, 1
), 0),
HSO._endminus = ISNULL(dbo.CalculateLeaveMinus
(HS.country, HS.designation,
CASE
WHEN HS.date_of_joining = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_joining AS DATE)
END,
CASE
WHEN HS.date_of_leaving = '0000-00-00'
THEN '2021-01-01'
ELSE CAST(HS.date_of_leaving AS DATE)
END, 2
), 0),
HSO.datemodi = GETDATE(),
HSO.CountryID = ISNULL(
(
SELECT bintid_pk
FROM country_master
WHERE country_code = HS.countrycode
), 777),
HSO.telephoneno = HS.telephone_no,
HSO.salutation = HS.salutation,
HSO.emptype = CASE
WHEN HS.designation = 'EH'
THEN 'DW'
ELSE 'NP'
END
--END : UPDATE EXISTING RECORDS--
WHEN NOT MATCHED BY SOURCE
--IN CASE JSON DOES NOT CONTAINS THE RECORD THEN DEACTIVATE THE USER
THEN UPDATE SET
HSO.btStatus = 0;
--START : UPDATE SUPERVISOR AFTER ALL DATA TRANSFER TO THE TABLE--
UPDATE HSO
SET
HSO.supervisor = ISNULL(O2.[bintId_Pk], 0)
FROM [HRSAP_DATA] AS HSO
LEFT JOIN [HR_SAP] AS H ON TRY_CAST(HSO.[SAPno] AS BIGINT) = CAST(H.[SAP_no] AS BIGINT)
LEFT JOIN [HRSAP_DATA] AS O2 ON TRY_CAST(O2.[SAPno] AS BIGINT) = CAST(H.[supervisor_sap_no] AS BIGINT);
--END : UPDATE SUPERVISOR AFTER ALL DATA TRANSFER TO THE TABLE--
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN;
INSERT INTO ExceptionLog
(ErrorLine,
ErrorMessage,
ErrorNumber,
ErrorProcedure,
ErrorSeverity,
ErrorState,
DateErrorRaised
)
VALUES
(ERROR_LINE(),
ERROR_MESSAGE(),
ERROR_NUMBER(),
ERROR_PROCEDURE(),
ERROR_SEVERITY(),
ERROR_STATE(),
GETDATE()
);
END;
ELSE
BEGIN
COMMIT TRAN;
END;
Kindly suggest for best approach.
Thanks
December 17, 2021 at 5:18 am
Since a MERGE is basically an INSERT/UPDATE, and you have 3 possible caes, you might want to just skip the MERGE and use separate INSERT/UPDATE statements.
IF (TestInvalid)
BEGIN
INSERT INTO FailTable()....
END
ELSE
IF EXISTS().... UPDATE
ELSE
INSERT....
December 17, 2021 at 5:26 am
Since a MERGE is basically an INSERT/UPDATE, and you have 3 possible caes, you might want to just skip the MERGE and use separate INSERT/UPDATE statements.
IF (TestInvalid) BEGIN INSERT INTO FailTable().... END
ELSE IF EXISTS().... UPDATE ELSE
INSERT....
Thank you for your reply.
I have one more question :
Will this have any performance impact as we have around 30000 records and this procedure needs to run every 24 hours?
Performance is likely to improve. However, one thing, instead of putting all the code into a single query inside a procedure, create a wrapper procedure for all the IF ... THEN and create just single, simple, procedures for the INSERT/UPDATE commands. This way, each gets compiled based on the data passed to it, rather than compiled all at once.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2021 at 12:59 pm
Based on the code I would guess your ExceptionLog table is empty because the error handling issues the ROLLBACK before the error values are accessed. But it's not really guaranteed the values would be populated anyway because just testing @@error at that point is questionable. The whole script is questionable. pietlinden and Grant's advice are great places to start.
This returns a divide by zero error and the ROLLBACK and SELECT never execute
begin transaction
select 1/0;
IF @@ERROR <> 0
begin
rollback transaction /* doesn't even execute */
select 'Not accessable'
end
else
commit;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply