Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Merge Statement Help


Merge Statement Help

Author
Message
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
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.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6016 Visits: 8314
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
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
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
);
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6016 Visits: 8314
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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 6490
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search