Here is the code. The code was done long time ago by someone left. It's not very well written and maybe can be improved. But now the issue seems to be not relevant to the code because when I manually run the code it runs pretty quick.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_xxx]
AS
/*=====================
Begin TRANSACTION
=====================*/
BEGIN TRANSACTION
DECLARE @error_txt VARCHAR(4000)
/*===========================================
CREATE # TABLE/s TO STORE CHANGED RECORDS
===========================================*/
CREATE TABLE #tmp1(...)
CREATE TABLE #tmp2(...)
/*===========================================
INSERT CHANGED RECORDS INTO # TABLE/s
===========================================*/
SET NOCOUNT ON
INSERT INTO #tmp1
SELECT * FROM r.dbo.product_master_1
EXCEPT
SELECT * FROM r.dbo.g_product_master_1
INSERT INTO #tmp2
SELECT * FROM r.dbo.product_detail_1
EXCEPT
SELECT * FROM r.dbo.g_product_detail_1
SET NOCOUNT OFF
BEGIN TRY
/*===========================================
EXISTING RECORDS - g_product_master_1
===========================================*/
IF EXISTS(SELECT *
FROM #tmp1 pm
JOIN r.dbo.g_product_master_1 gbpm ON pm.prod_id = gbpm.prod_id)
BEGIN
SELECT *
FROM #tmp1
UPDATE gbpm
SET gbpm.level1_id = pm.level1_id,
gbpm.level2_id = pm.level2_id,
...
FROM r.dbo.g_product_master_1 gbpm
JOIN #tmp1 pm ON gbpm.prod_id = pm.prod_id
END
IF @@ROWCOUNT = 0
PRINT 'No new rows updated into r.dbo.g_product_master_1'
ELSE PRINT 'Row/s updated into r.dbo.g_product_master_1'
/*===========================================
NEW RECORDS - g_product_master_1
===========================================*/
IF EXISTS (SELECT *
FROM #tmp1 pm
JOIN r.dbo.g_product_master_1 gbpm ON pm.prod_id <> gbpm.prod_id)
BEGIN
SET IDENTITY_INSERT r.dbo.g_product_master_1 ON
INSERT INTO r.dbo.g_product_master_1
(...)
SELECT
pm.prod_id
,pm.level1_id
...
FROM #tmp1 pm
WHERE NOT EXISTS (SELECT * FROM r.dbo.g_product_master_1 gbpm WHERE pm.prod_id = gbpm.prod_id)
END
IF @@ROWCOUNT = 0
PRINT 'No new rows inserted into r.dbo.g_product_master_1'
ELSE PRINT 'New row/s inserted into r.dbo.g_product_master_1'
SET IDENTITY_INSERT r.dbo.g_product_master_1 OFF
/*===========================================
EXISTING RECORDS - g_product_detail_1
===========================================*/
IF EXISTS (SELECT *
FROM #tmp2 pd
JOIN r.dbo.g_product_detail_1 gbpd ON pd.sku_id = gbpd.sku_id)
BEGIN
SELECT *
FROM #tmp2
UPDATE gbpd
SET gbpd.product_id = pd.product_id,
gbpd.colour_id = pd.colour_id,
...
FROM r.dbo.g_product_detail_1 gbpd
JOIN #tmp2 pd ON gbpd.sku_id = pd.sku_id
END
IF @@ROWCOUNT = 0
PRINT 'No new rows updated into r.dbo.g_product_detail_1'
ELSE PRINT 'Row/s updated into r.dbo.g_product_detail_1'
/*===========================================
NEW RECORDS - g_product_detail_1
===========================================*/
IF EXISTS (SELECT *
FROM #tmp2 pd
JOIN r.dbo.g_product_detail_1 gbpd ON pd.sku_id <> gbpd.sku_id)
BEGIN
SET IDENTITY_INSERT r.dbo.g_product_detail_1 ON
INSERT INTO r.dbo.g_product_detail_1
(...)
SELECT
pd.sku_id
,pd.product_id
...
FROM #tmp2 pd
WHERE NOT EXISTS (SELECT * FROM r.dbo.g_product_detail_1 gbpd WHERE pd.sku_id = gbpd.sku_id)
END
IF @@ROWCOUNT = 0
PRINT 'No new rows inserted into r.dbo.g_product_detail_1'
ELSE PRINT 'New row/s inserted into r.dbo.g_product_detail_1'
--SET IDENTITY_INSERT r.dbo.g_product_detail_1 OFF
END TRY
BEGIN CATCH
SELECT @error_txt = CONVERT(VARCHAR(20),ERROR_SEVERITY()) + '= ' + ERROR_MESSAGE()
RAISERROR(@error_txt,16,1)
ROLLBACK TRANSACTION
RETURN
END CATCH
COMMIT TRANSACTION
/*===========================================
DROP # TABLES
===========================================*/
DROP TABLE #tmp1
DROP TABLE #tmp2