Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge Statement Help Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 6:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 208, Visits: 760
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.
Post #1551421
Posted Friday, March 14, 2014 7:17 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 4,343, Visits: 6,150
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 at GMail
Post #1551425
Posted Friday, March 14, 2014 10:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 208, Visits: 760
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
);
Post #1551437
Posted Saturday, March 15, 2014 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 4,343, Visits: 6,150
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 at GMail
Post #1551486
Posted Monday, March 17, 2014 7:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:58 AM
Points: 2,467, Visits: 6,437
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


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1551722
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse