Need help with MERGE statements.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with MERGE statements.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-22 : 16:43:16

[code]Hi,

Given the table and the business rule below. How can I get the Merge statement to work as expect
or different way to write a query. I am using SQL 2008.

Any help is appreciated.

I am expected output based on the provided sample data?

IF OBJECT_ID('Tempdb.dbo.#History', 'u') IS NOT NULL
DROP TABLE #History
GO
CREATE TABLE #History
(
LoanNum VARCHAR(10) NULL,
REODate DATETIME NULL,
DilRecorded DATETIME NULL,
InsertBy VARCHAR(20) NULL,
InsertDt DATETIME NULL,
)
GO

INSERT #History (LoanNum, REODate, DilRecorded ) VALUES ('120606', NULL, '2010-11-12'),
('120605', NULL, NULL),
('120610', '2010-11-14', '2010-11-18'),
('120622', '2010-09-10', '2010-09-10');
GO

--Testing

SELECT *
FROM #History;
GO

LoanNum REODate DilRecorded InsertBy InsertDt
---------- ----------------------- ----------------------- -------------------- ---------
120606 NULL 2010-11-12 00:00:00.000 NULL NULL
120605 NULL NULL NULL NULL
120610 2010-11-14 00:00:00.000 2010-11-18 00:00:00.000 NULL NULL
120622 2010-09-10 00:00:00.000 2010-09-10 00:00:00.000 NULL NULL

Business rules:

--1. Insert into #History table ONLY if the values passed in is NOT the same values in the table.
-- LoanNum <> @LoanNum and REODate <> @REODate and @DilRecorded <> @DilRecorded

Ex:

--Test Case #1

--#1
DECLARE @LoanNum AS VARCHAR(10) = '120606'
DECLARE @LoginId AS VARCHAR(50) = 'Usertest1'
DECLARE @REODate AS DATETIME = NULL
DECLARE @DilRecorded AS DATETIME = '2010-11-12'

MERGE INTO #History AS t
USING (VALUES( @LoanNum , @REODate, @DilRecorded, @LoginId )) AS s (LoanNum , LoginId, REODate, DilRecorded)
ON t.LoanNum = s.LoanNum
--AND CONVERT(CHAR(8), ISNULL(t.REODate, '19000101'), 112) = CONVERT(CHAR(8), ISNULL(@REODate, '19000101'), 112)
AND CONVERT(CHAR(8),t.REODate, 112) = CONVERT(CHAR(8), @REODate, 112)
AND CONVERT(CHAR(8), t.DilRecorded, 112) = CONVERT(CHAR(8), @DilRecorded, 112)

WHEN NOT MATCHED BY TARGET
THEN INSERT (LoanNum, REODate, DilRecorded, InsertBy, InsertDt)
VALUES (s.LoanNum, @REODate, @DilRecorded, @LoginId, GETDATE());
GO

-- It should not insert a record into a #History table because the values I am passing in the samev values in the table.
-- I check only if LoanNum or REODate or DilRecorded has changed then insert otherwise don't.

-- I think I know why because comparing NULL but I need your advice on how to fix the problem.

SELECT *
FROM #History
WHERE LoanNum = '120606';
GO

-- Expect results: one row only.

LoanNum REODate DilRecorded InsertBy InsertDt
---------- ----------------------- ----------------------- -------------------- -----------------------
120606 NULL 2010-11-12 00:00:00.000 NULL NULL

--Case#2

DECLARE @LoanNum AS VARCHAR(10) = '120605'
DECLARE @LoginId AS VARCHAR(50) = 'Usertest2'
DECLARE @REODate AS DATETIME = NULL
DECLARE @DilRecorded AS DATETIME = NULL

MERGE INTO #History AS t
USING (VALUES( @LoanNum , @REODate, @DilRecorded, @LoginId )) AS s (LoanNum , LoginId, REODate, DilRecorded)
ON t.LoanNum = s.LoanNum
--AND CONVERT(CHAR(8), ISNULL(t.REODate, '19000101'), 112) = CONVERT(CHAR(8), ISNULL(@REODate, '19000101'), 112)
AND CONVERT(CHAR(8),t.REODate, 112) = CONVERT(CHAR(8), @REODate, 112)
AND CONVERT(CHAR(8), t.DilRecorded, 112) = CONVERT(CHAR(8), @DilRecorded, 112)

WHEN NOT MATCHED BY TARGET
THEN INSERT (LoanNum, REODate, DilRecorded, InsertBy, InsertDt)
VALUES (s.LoanNum, @REODate, @DilRecorded, @LoginId, GETDATE());
GO

-- Same issue as above.
SELECT *
FROM #History
WHERE LoanNum = '120605';
GO

LoanNum REODate DilRecorded InsertBy InsertDt
---------- ----------------------- ----------------------- -------------------- -----------------------
120605 NULL NULL NULL NULL
120605 NULL NULL Usertest2 2010-12-22 13:33:41.230 -- should not insert. [/code]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-12-22 : 23:39:43

quote:

MERGE INTO #History AS t
USING (VALUES( @LoanNum , @REODate, @DilRecorded, @LoginId )) AS s (LoanNum , LoginId, REODate, DilRecorded)
ON t.LoanNum = s.LoanNum

--AND CONVERT(CHAR(8), ISNULL(t.REODate, '19000101'), 112) = CONVERT(CHAR(8), ISNULL(@REODate, '19000101'), 112)
AND CONVERT(CHAR(8),t.REODate, 112) = CONVERT(CHAR(8), @REODate, 112)

AND CONVERT(CHAR(8), t.DilRecorded, 112) = CONVERT(CHAR(8), @DilRecorded, 112)

WHEN NOT MATCHED BY TARGET
THEN INSERT (LoanNum, REODate, DilRecorded, InsertBy, InsertDt)
VALUES (s.LoanNum, @REODate, @DilRecorded, @LoginId, GETDATE());

Replace red part by this one for better performance

AND ((t.REODate is null and @REODate is null) or (t.REODate = @REODate) )

Do the same for other null columns

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-23 : 11:29:48

Do LoanNum, REODate, DilRecorded make up the key for your table? I assume not because of their nullability. But, you didn't provide the key information, so it's hard to tell. What is they key for your table?

To me it looks like there is an issue on the search condition.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-23 : 11:51:25

Thank you namman for your suggestion, I will test it out.

The LoanNum is the PK for the table.

Thanks.

Happy Holidays.

quote:
Originally posted by Lamprey

Do LoanNum, REODate, DilRecorded make up the key for your table? I assume not because of their nullability. But, you didn't provide the key information, so it's hard to tell. What is they key for your table?

To me it looks like there is an issue on the search condition.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-23 : 11:57:25

That's what I thought. If it helps, here is a Merge statement with an update and an insert. It doesn't meet your requirements, but in the interest of learning...

MERGE
	#History AS T
USING
	(VALUES (@LoanNum, @REODate, @DilRecorded, @LoginId)) AS S (LoanNum , REODate, DilRecorded, LoginId)
	ON T.LoanNum = S.LoanNum
WHEN MATCHED THEN
	UPDATE SET
		REODate = S.REODate,
		DilRecorded = S.DilRecorded,
		InsertBy = S.LoginID,
		InsertDT = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
	INSERT (LoanNum, REODate, DilRecorded, InsertBy, InsertDt)
	VALUES (S.LoanNum, S.REODate, S.DilRecorded, S.LoginId, CURRENT_TIMESTAMP);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources