Help Using Merge

[robert030975]

Hi In need of help or a bit of advice in the way forward

I have a 50 row table mill table with over 100 columns and want to do a merge now the first method is accpetable although a little cluncky , I als have concern over time . I then thought of using on the when matched trying checksum to quickly pick up the changes without using the above column a <> column b with there being over 100 columns but as its a subquey it fails due to it bring back more than one value.


CREATE TABLE #tmp --- source
(
LDate Date NOT NULL ,
id INT NOT NULL ,
Tp NVARCHAR(25) NULL )




INSERT #tmp ( Ldate, id, tp) VALUES ( GETDATE() , 1 , 'Test' )
INSERT #tmp ( Ldate, id, tp) VALUES ( GETDATE() , 2 , 'Test' )
INSERT #tmp ( Ldate, id, tp) VALUES ( GETDATE() , 3 , 'Test' )


CREATE TABLE #tmp1
(
a_id INt IDENTITY(1,1) NOT NULL,
LDate Date NOT NULL ,
id INT NOT NULL ,
Tp NVARCHAR(25) NULL )



--Select * from #tmp
---- Method 1
MERGE #tmp1 AS DST --destination
USING #tmp AS SRC --source
ON (SRC.ID = DST.id)
WHEN NOT MATCHED THEN
INSERT ( Ldate, id, tp) VALUES (SRC.Ldate, SRC.id , SRC.tp)

WHEN MATCHED

AND
ISNULL(DST.Ldate,'') <> ISNULL(SRC.Ldate,'')
OR ISNULL(DST.id,'') <> ISNULL(SRC.id,'')
OR ISNULL(DST.Tp,'') <> ISNULL(SRC.Tp,'')
THEN UPDATE
SET
DST.Ldate = SRC.Ldate
,DST.id = SRC.id
,DST.Tp = SRC.Tp
;

--- Method 2 fails
MERGE #tmp1 AS DST --destination
USING #tmp AS SRC --source
ON (SRC.ID = DST.id)
WHEN NOT MATCHED THEN
INSERT ( Ldate, id, tp) VALUES (SRC.Ldate, SRC.id , SRC.tp)

WHEN MATCHED


AND
(select binary_checksum (Ldate , ID, tp ) from #tmp)
<> (select binary_checksum (Ldate , ID, tp ) from #tmp1)

--ISNULL(DST.Ldate,'') <> ISNULL(SRC.Ldate,'')
--OR ISNULL(DST.id,'') <> ISNULL(SRC.id,'')
--OR ISNULL(DST.Tp,'') <> ISNULL(SRC.Tp,'')
THEN UPDATE
SET
DST.Ldate = SRC.Ldate
,DST.id = SRC.id
,DST.Tp = SRC.Tp
;

Any help or suggestions would be great

 

[fredericofonseca]

firstly - why are you checking if ID is null if that is the join condition?

second - try
(ID removed as it is part of the join condition)
AND binary_checksum (src.Ldate , src.tp )
<> binary_checksum (dst.Ldate , dst.tp )

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886