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 2005 Forums

Transact-SQL (2005)

Need help with MERGE statements.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-22 : 16:42:48

[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]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-22 : 16:49:06

Duplicate, locking topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources