December 29, 2014 at 8:10 pm
edit: sorry this is happening in SQL Server 2008 R2, but it will also apply here.
I have need to insert records when not in the target table.
sample ddl for source and target tables. Notice the target table is missing the 'Upstairs' Rows:
CREATE TABLE source (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)
INSERT INTO dbo.source
( property_id ,
unit_type ,
care_type_id ,
propert_room_type_id ,
rate
)
VALUES ( 446521 , 'Upstairs AL' , 'A' , 6, 60 ),
( 446521 , 'Upstairs IL' , 'R' , 6, 70 ),
( 446521 , 'One Bdrm AL' , 'A' , 2, 50 ),
( 446521 , 'One Bdrm IL' , 'R' , 2, 40 ),
( 446521 , 'InLaw AL' , 'A' , 1, 30 ),
( 446521 , 'InLaw IL' , 'R' , 1, 20 ),
( 446521 , 'Suite AL' , 'R' , 3, 55 )
;
CREATE TABLE target (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)
INSERT INTO dbo.target
( property_id ,
unit_type ,
care_type_id ,
propert_room_type_id ,
rate
)
VALUES ( 446521 , 'One Bdrm AL' , 'A' , 2, 50 ),
( 446521 , 'One Bdrm IL' , 'R' , 2, 40 ),
( 446521 , 'InLaw AL' , 'A' , 1, 30 ),
( 446521 , 'InLaw IL' , 'R' , 1, 20 ),
( 446521 , 'Suite AL' , 'R' , 3, 55 )
;
merge update insert statement as follows:
MERGE INTO dbo.target t
USING (
SELECT * FROM dbo.source
) src
ON src.property_id = t.property_id
--if spreadsheet price is different than what's in tbl and greater than 0, and if mapping for company number exists
WHEN MATCHED AND t.rate <> src.rate AND src.rate >0 AND src.property_id <> 0
AND src.property_id = t.property_id
AND src.care_type_id = t.care_type_id
AND src.propert_room_type_id = t.propert_room_type_id
THEN UPDATE SET
t.rate = src.rate
--when destination table doesn't have all entries in available in source
WHEN NOT MATCHED BY TARGET THEN
INSERT (property_id, propert_room_type_id, care_type_id, rate)
VALUES(
src.property_id,
src.propert_room_type_id,
src.care_type_id,
src.rate)
--to display updates made back to ssms, uncomment below
OUTPUT Inserted.property_id,Inserted.propert_room_type_id, Inserted.care_type_id, Inserted.rate
;
problem is that this will not insert the 'Upstairs' rows into target table. Will someone advise how to insert?
December 29, 2014 at 10:39 pm
Quick thought, the property_id values are the same in both sets and therefore it will never hit the NOT MATCHED clause. To fix this, add another column to the criteria.
😎
MERGE dbo.target AS t
USING (
SELECT
SRC.property_id
,SRC.unit_type
,SRC.care_type_id
,SRC.propert_room_type_id
,SRC.rate
FROM dbo.source SRC
) AS src
ON src.property_id = t.property_id
/* all the property_id values are the same therefore another
key is needed
*/
AND src.unit_type = t.unit_type
--if spreadsheet price is different than what's in tbl and greater than 0, and if mapping for company number exists
WHEN MATCHED
AND t.rate <> src.rate
AND src.rate > 0
AND src.property_id <> 0
/*
This is not needed as it is part of the match condition.
AND src.property_id = t.property_id
*/
AND src.care_type_id = t.care_type_id
AND src.propert_room_type_id = t.propert_room_type_id
THEN UPDATE SET
t.rate = src.rate
--when destination table doesn't have all entries in available in source
WHEN NOT MATCHED THEN
INSERT (property_id, unit_type, propert_room_type_id, care_type_id, rate)
VALUES(
src.property_id,
src.unit_type,
src.propert_room_type_id,
src.care_type_id,
src.rate)
--to display updates made back to ssms, uncomment below
OUTPUT Inserted.property_id,Inserted.propert_room_type_id, Inserted.care_type_id, Inserted.rate
;
December 29, 2014 at 11:46 pm
Yes, thanks. I did this and it was solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply