Merge SQL Query Help

Transact-SQL
1

Hi Guys,

I need help. I want to use a MERGE statement. The source and target tables are pretty big so I really want to use MERGE Statement
Here is the issue. I want to compare SOURCE VS Target table with some filters in the target table. I am not sure how and where I can
use a filter. in MERGE Statement. Below are my query and sample data.

DECLARE @Targettable TABLE
(
id [int] IDENTITY(1,1) NOT NULL,
city VARCHAR(25),
PhoneNumber VARCHAR(15),
state VARCHAR(5),
bid INTEGER NULL
)

DECLARE @FilterTable TABLE
(
id [int] IDENTITY(1,1) NOT NULL,
Fname VARCHAR(50)
)

INSERT INTO @Targettable
( city, PhoneNumber, state, bid )
select 'San Pedro','7609876788', 'CA',1
UNION
select 'Los Angles','7609876767', 'CA',1
UNION
select 'New York','7609876908', 'NY',1
UNION
select 'Los Angles','7609876767', 'CA',2
UNION
select 'Los Am','920786777', 'CA',2

INSERT INTO @FilterTable
( Fname )
SELECT 'FULL_EXECUTION123.CSV'
UNION
SELECT 'EXIT_EXECUTION123.CSV'

--SELECT * FROM @Targettable
--SELECT * FROM @FilterTable

--The final query that I want to use as my TARGET Table START
--SELECT
-- T.city
-- ,T.PhoneNumber
-- ,T.state
-- FROM @Targettable T
-- INNER JOIN @FilterTable F
-- ON T.bid = F.id
-- AND SUBSTRING(F.Fname,1,4) = 'FULL'
--The final query that I want to use as my TARGET Table END

--Source Table
DECLARE @SourceTable TABLE
(
id [int] IDENTITY(1,1) NOT NULL,
city VARCHAR(25),
PhoneNumber VARCHAR(15),
state VARCHAR(5)
)

INSERT INTO @SourceTable
( city, PhoneNumber, state )
SELECT 'San Pedro','7609876788','CA'
UNION
SELECT 'Los Angles','7609876767','CA'
UNION
SELECT 'Palm Beach','8798887867','CA'
UNION
select 'Los Am','920786777', 'CA'

-- SELECT * FROM @SourceTable

--Merge STATEMENT
MERGE INTO @Targettable AS T
USING
(
SELECT
city
,PhoneNumber
,state
FROM @SourceTable
) AS S
ON
( S.city = T.city
AND S.PhoneNumber = T.PhoneNumber
AND S.state = T.state
)
WHEN NOT MATCHED THEN
INSERT (city,PhoneNumber,state,bid)
VALUES (S.city,S.PhoneNumber,S.state,7)
;

SELECT * FROM @Targettable

Here what I want at the end result

id city PhoneNumber state bid
1 Los Am 920786777 CA 2
2 Los Angles 7609876767 CA 1
3 Los Angles 7609876767 CA 2
4 New York 7609876908 NY 1
5 San Pedro 7609876788 CA 1
6 Palm Beach 8798887867 CA 7
7 Los Am 920786777 CA 7

Please let me know if my question is not clear.
Thank You.

2

Not real sure what your requirements are...but you can do something like this:

MERGE ..
WHEN NOT MATCHED BY TARGET
AND {additional criteria}
THEN
INSERT/UPDATE/DELETE

Or - you can put your source into a CTE, use the CTE and additional criteria for the USING:

WITH somename
AS (
{source query}
)
MERGE {target table} As tgt
USING somename As src
On {key value criteria}
WHEN NOT MATCHED BY TARGET THEN
INSERT
WHEN NOT MATCHED BY SOURCE THEN
DELETE/UPDATE
WHEN MATCHED
UPDATE

I have used this before:

When Matched
And (source.col5 <> target.col5
Or source.col6 <> target.col6
Or source.col7 <> target.col7)
Then
Update

This will only update the data if one of the specified columns has actually changed values...

Lots of options

3

Just in case discussion leads to something interesting :slight_smile:

on such "at least one column has changed" inequality-tests we force a Binary Collation (so that the column data being compared must be same-case etc.) and also add a "One or the other, but not both, is NULL" . The NULL test could be skipped on NOT NULL columns but we mechanically generate the comparison code and always leave the NULL test in - on the basis that something might change in the future, wittingly-or-not ....

4

Yeah - I did not include those specifics...but definitely you need to consider nullable columns for any comparisons and how you want those to be considered.

1 Like