Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi All,
I am facing a issue with Merge statement below is my merge statement and its insert and update syntax is running fine but delete is not working. Can anybody suggest me what need to modify
 
string Sql_Statement = @" MERGE INTO Organization With (ROWLOCK) AS Target " +
                             " USING (VALUES " + valuesCommunities.Substring(0, valuesCommunities.Length - 1) + " ) " +
                             " AS Source (LegacyOrganizationID, CustomerID, OrganizationName, Address1, Address2, City, [State], CountryID, Zip, Phone, Fax, DateAdded, AddedBy, DateModified, ModifiedBy, DateDeleted, DeletedBy, IsActive, ReferenceID) " +
                             " ON Target.legacyOrganizationId=Source.legacyOrganizationID " +
                             " AND Target.CustomerId=Source.CustomerID " +
                             " WHEN MATCHED THEN " +
                             " UPDATE SET LegacyOrganizationID = Source.LegacyOrganizationID, CustomerID = Source.CustomerID, OrganizationName = Source.OrganizationName, Address1 = Source.Address1, Address2 = Source.Address2, City = Source.City, State = Source.State, CountryID = Source.CountryID, Zip = Source.Zip, Phone = Source.Phone, Fax = Source.Fax,  DateModified = Source.DateModified, ModifiedBy = Source.ModifiedBy, DateDeleted = Source.DateDeleted, DeletedBy = Source.DeletedBy, IsActive = Source.IsActive" +
                             " WHEN NOT MATCHED BY TARGET THEN " +
                             " INSERT (LegacyOrganizationID, CustomerID, OrganizationName, Address1, Address2, City, [State], CountryID, Zip, Phone, Fax, DateAdded, AddedBy, DateModified, ModifiedBy, DateDeleted, DeletedBy, IsActive) " +
                             " VALUES (Source.LegacyOrganizationID, Source.CustomerID, Source.OrganizationName, Source.Address1, Source.Address2, Source.City, Source.[State], Source.CountryID, Source.Zip, Source.Phone, Source.Fax, Source.DateAdded, Source.AddedBy, Source.DateModified, Source.ModifiedBy, Source.DateDeleted, Source.DeletedBy, Source.IsActive) " +
                             " WHEN NOT MATCHED BY Source THEN Delete" +
                             " OUTPUT $action, Inserted.OrganizationID, Inserted.LegacyOrganizationID,Source.ReferenceID; ";

 
Waiting for your kind sugggestion
 
Thanks
AP
Posted 27-Nov-12 2:23am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

That statement should work just fine. The syntax is correct, you don't have a duplicate statement in there, and since you don't get a compile or runtime error you know the query is being accepted by SQL. My suggestion would be to do some experiments (if you haven't already). Create a row in your destination that you know for sure wouldn't be in your source. Make sure that the two IDs are definitely not in your source and then try it again. Maybe you are getting a match that you didn't expect because your match criteria is too generic.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,656
1 Sergey Alexandrovich Kryukov 6,490
2 CPallini 5,310
3 George Jonsson 3,584
4 Gihan Liyanage 2,650


Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100