Create eaiomyk Table:
CREATE TABLE [dbo].[eaiomyk](
[eaiOMYK_SN] [varchar](50) NULL,
[eaiOMYK_SN_OM] [varchar](50) NULL,
[eaiOMYK_OffCode] [varchar](50) NULL,
[eaiOMYK_OffType] [varchar](50) NULL,
[eaiOMYK_ApproveDate] [varchar](50) NULL,
[eaiOMYK_LastUpdate] [varchar](50) NULL,
[eaiOMYK_OSBalance] [varchar](50) NULL,
[eaiOMYK_DelinqAmount] [varchar](50) NULL,
[eaiOMYK_DelinqIndex] [varchar](50) NULL,
[eaiOMYK_TotalItems] [varchar](50) NULL,
[crmBP_ID] [dbo].[crmGUIDREF] NULL,
[crmBE_ID] [dbo].[crmGUIDREF] NULL
) ON [PRIMARY]
GO
Update Table:
BEGIN TRANSACTION
UPDATE eaiomyk set
crmBP_ID = (select TOP 1 crwBEX_CustomerID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode),
crmBE_ID = (select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode)
--SELECT crmBP_ID, crmBE_ID, eaiOMYK_OffCode,
--(select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode),
FROM eaiomyk
where (select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode) is NOT NULL
-- commit
select * from eaiomyk
where crmBE_ID is not null
begin transaction
INSERT INTO crmPARV
(crmPARV_ID, crmOBJP_ID, crmPARV_RowID, crmPARV_IntValue)
SELECT replace(newid(), '-', '' ), 'crwBPX.crwBPX_HRBP_TotalItems', crmBP_ID, eaiOMYK_TotalItems
FROM eaiomyk
WHERE crmBP_ID is NOT NULL
AND crmBE_ID IN (SELECT TOP 1 LOComyk.crmBE_ID from eaiomyk LOComyk where LOComyk.crmBP_ID = eaiomyk.crmBP_ID ORDER BY eaiOMYK_SN)
-- commit
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID IN ('crwBPX.crwBPX_HRBP_LastUpdate', 'crwBPX.crwBPX_HRBP_TotalItems')
-- 55324
-- commit / rollback
Deletion of existing:
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID = 'crwBPX.crwBPX_HRBP_LastUpdate'
-- 00:07
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID = 'crwBPX.crwBPX_HRBP_TotalItems'
-- 03:04
CREATE TABLE [dbo].[eaiomyk](
[eaiOMYK_SN] [varchar](50) NULL,
[eaiOMYK_SN_OM] [varchar](50) NULL,
[eaiOMYK_OffCode] [varchar](50) NULL,
[eaiOMYK_OffType] [varchar](50) NULL,
[eaiOMYK_ApproveDate] [varchar](50) NULL,
[eaiOMYK_LastUpdate] [varchar](50) NULL,
[eaiOMYK_OSBalance] [varchar](50) NULL,
[eaiOMYK_DelinqAmount] [varchar](50) NULL,
[eaiOMYK_DelinqIndex] [varchar](50) NULL,
[eaiOMYK_TotalItems] [varchar](50) NULL,
[crmBP_ID] [dbo].[crmGUIDREF] NULL,
[crmBE_ID] [dbo].[crmGUIDREF] NULL
) ON [PRIMARY]
GO
Update Table:
BEGIN TRANSACTION
UPDATE eaiomyk set
crmBP_ID = (select TOP 1 crwBEX_CustomerID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode),
crmBE_ID = (select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode)
--SELECT crmBP_ID, crmBE_ID, eaiOMYK_OffCode,
--(select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode),
FROM eaiomyk
where (select TOP 1 crmBE_ID from crwBEX where crwBEX_COFFRefNo = eaiOMYK_OffCode) is NOT NULL
-- commit
select * from eaiomyk
where crmBE_ID is not null
begin transaction
INSERT INTO crmPARV
(crmPARV_ID, crmOBJP_ID, crmPARV_RowID, crmPARV_IntValue)
SELECT replace(newid(), '-', '' ), 'crwBPX.crwBPX_HRBP_TotalItems', crmBP_ID, eaiOMYK_TotalItems
FROM eaiomyk
WHERE crmBP_ID is NOT NULL
AND crmBE_ID IN (SELECT TOP 1 LOComyk.crmBE_ID from eaiomyk LOComyk where LOComyk.crmBP_ID = eaiomyk.crmBP_ID ORDER BY eaiOMYK_SN)
-- commit
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID IN ('crwBPX.crwBPX_HRBP_LastUpdate', 'crwBPX.crwBPX_HRBP_TotalItems')
-- 55324
-- commit / rollback
Deletion of existing:
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID = 'crwBPX.crwBPX_HRBP_LastUpdate'
-- 00:07
begin transaction
delete crmPARV
-- select count(*) from crmPARV
where crmOBJP_ID = 'crwBPX.crwBPX_HRBP_TotalItems'
-- 03:04