Click here to Skip to main content
12,458,167 members (54,197 online)
Rate this:
 
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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2016
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