October 27, 2017 at 10:57 pm
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.
October 30, 2017 at 8:43 am
If you want help, first you have to have something that has some shot at working. You've described a filter table with only an Fname column, but are joining to an id column of some kind, that doesn't exist in your declared table. Also, please use the IF Codes to surround query text with a bracketed code="SQL" and /code set of tags, as that will improve readability considerably. Please explain in more detail...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 30, 2017 at 10:52 am
The query looks to be working correctly to me. You show the results you are expecting to have '7 Los Am 920786777 CA 7'. But this record would not be inserted from the source since it exists in the target table.DECLARE @Targettable TABLE
(
id [int] IDENTITY(1,1) NOT NULL,
city VARCHAR(25),
PhoneNumber VARCHAR(15),
state VARCHAR(5),
bid INTEGER NULL
)
;
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
;
--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'
;
--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
Results:
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
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 30, 2017 at 10:56 am
I removed everything for @FilterTable since it was not used or needed for the MERGE statement.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 30, 2017 at 10:37 pm
Thanks for your help and sorry for not providing a full Info.
First of all, I created a sample data and sample scenario.
October 31, 2017 at 8:57 am
Use a CTE to filter your target table.
WITH Tgt AS (
select T.*
FROM @Targettable T
INNER JOIN @FilterTable f on t.bid = f.id
WHERE f.filename LIKE 'FULL%'
)
MERGE INTO Tgt
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)
;
Drew
PS: I changed your SUBSTRING to a LIKE, because SUBSTRING is not SARGable, but LIKE is as long as the search pattern does not begin with a wildcard.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2017 at 8:09 pm
Thanks Drew. It works.
November 3, 2017 at 1:54 am
Use a CTE to filter your target table.
Is the CTE able to make use of an appropriate index in this case? I am guessing yes but just wondered.
----------------------------------------------------
November 3, 2017 at 8:24 am
MMartin1 - Friday, November 3, 2017 1:54 AMUse a CTE to filter your target table.
Is the CTE able to make use of an appropriate index in this case? I am guessing yes but just wondered.
Yes, CTE's are essentially temporary views.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2017 at 4:02 pm
Thanks!
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply