July 5, 2011 at 12:47 am
CREATE TABLE #Changes
(
account_id INTEGER PRIMARY KEY,
the_address NVARCHAR(100) NOT NULL
);
CREATE TABLE #Data
(
account_id INTEGER NOT NULL,
address_type VARCHAR(20) NOT NULL,
the_address NVARCHAR(100) NOT NULL,
PRIMARY KEY (account_id, address_type)
);
BEGIN TRANSACTION;
INSERT #Data
(account_id, address_type, the_address)
OUTPUT 'INSERT', INSERTED.*
SELECT
c.account_id,
CASE
WHEN c.the_address LIKE 'PO%' THEN 'Mailing'
ELSE 'Main'
END,
c.the_address
FROM #Changes AS c
WHERE
NOT EXISTS
(
SELECT
1
FROM #Data AS d WITH (UPDLOCK, HOLDLOCK)
WHERE
d.account_id = c.account_id
);
UPDATE d
SET the_address = c.the_address
OUTPUT 'UPDATE', INSERTED.*
FROM #Data AS d
INNER JOIN #Changes AS c ON
c.account_id = d.account_id
WHERE
(d.address_type = 'Mailing' AND c.the_address LIKE 'PO%')
OR (d.address_type = 'Main' AND c.the_address NOT LIKE 'PO%');
COMMIT TRANSACTION;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply