Merge Statement Help - SQL Server Forums

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

Transact-SQL (2012)

Merge Statement Help

Author

Topic

tooba111
Starting Member

22 Posts

Posted - 2014-03-14 : 20:20:12

Hi Guys,

I am using Merge Statement. Here is my requirement, I don't want to Insert data if Client State is NY, but I want to update all data

When Not Matched
and State not in ('NY')
THEN INSERT

the problem is sometime data NY data is inserted and sometime don't. Is anyone can help, am i doing right or not. Any help would be great appreciate.

Thank You.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-14 : 20:23:04

can you show us the table schema and existing query ?

KH
[spoiler]Time is always against us[/spoiler]

tooba
Posting Yak Master

224 Posts

Posted - 2014-03-15 : 00:24:11

Here is the sample data that I just create FYI its a Sample data. The requirement are I don't want to Insert Customer data if Customer State is NY and KT, but I want to Update Customer data if customer live in State NY and KT.. Its working fine (On sample data this Merge Statement is working fine)

Create Table TblCustomer
(
ID INT IDENTITY(1,1),
Fname varchar(20),
Lname varchar(20),
City varchar(20),
State varchar(20)
)

Create Table TblSource
(
ID INT IDENTITY(1,1),
Fname varchar(20),
Lname varchar(20),
City varchar(20),
State varchar(20)
)

Insert TblSource
values ('Smith','James','Abc','NY')

Insert TblSource
values ('Smith','James','Abc','NY')

Insert TblSource
values ('Smith','James','xy','CA')

Insert TblSource
values ('Smith','James','Chicago','KT')

Insert TblSource
values ('Smith','James','Abc','CA')

--My Merge Statement

--SELECT * FROM TblSource
--SELECT * FROM TblCustomer

Merge INTO TblCustomer C
Using (
Select * from TblSource) S
ON (C.Fname = S.Fname and C.Lname = S.Lname)
WHEN MATCHED THEN
UPDATE SET C.FNAME = S.FNAME
WHEN NOT MATCHED
AND S.STATE NOT IN ('NY','kt')
THEN INSERT
(
FNAME,
LNAME,
CITY,
STATE
)
VALUES
(
S.FNAME,
S.LNAME,
S.CITY,
S.STATE
);

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-15 : 02:15:34

Hi

I will try something like this


Merge INTO TblCustomer C
 Using (
 Select * from TblSource) S
 ON (C.Fname = S.Fname and C.Lname = S.Lname)
 WHEN MATCHED 
     AND S.STATE IN ('NY','kt') THEN
   UPDATE SET C.FNAME = S.FNAME
 WHEN NOT MATCHED 
 AND S.STATE NOT IN ('NY','kt')
 THEN INSERT 
 ( 
 FNAME,
 LNAME,
 CITY,
 STATE
 )
VALUES
( 
 S.FNAME,
 S.LNAME,
 S.CITY,
 S.STATE
 );

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources