March 14, 2014 at 6:21 pm
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.
March 14, 2014 at 7:17 pm
Look around for a "how to post" article so you can learn how to help us help you. Can you give us a sample table(s), sample data, sample code you are trying and what you expect to happen with the data?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 14, 2014 at 10:24 pm
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),
Fnamevarchar(20),
Lnamevarchar(20),
Cityvarchar(20),
Statevarchar(20)
)
Create Table TblSource
(
IDINT IDENTITY(1,1),
Fnamevarchar(20),
Lnamevarchar(20),
Cityvarchar(20),
Statevarchar(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
);
March 15, 2014 at 8:55 am
If it works on your sample but not when you run it on real data then you need to find out the characteristics of the rows that do NOT work as you expect. That will tell you why it isn't working like you expect. My guess is that the fname/lname match is not hitting like you think it should. Most likely cause is padding with spaces or not, or maybe leading space(s) or possibly some non-printable characters in the data. look there first.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2014 at 7:28 am
Not sure what exactly you want. From the SQL statement you posted it will always insert data for NY and KT if it does not match Fname and Sname, but only update if the state is not NY or kt. Is that what you intended? Oh, btw, is your database case sensitive?
Regards,
Jan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply