Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using MERGE or maybe I should'nt

Posted on 2013-06-06
3
Medium Priority
?
258 Views
Last Modified: 2013-06-07
I know the following code doesn't work, but it represents what I need to do. I'm trying to use MERGE, but it just doesn't (as far as I know) have the capability of doing what I need to do.

MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON MRPsld.partno = ptpi.partno
WHEN MATCHED AND (ptpi.ReceivedDate = MRPsld.sndate
			AND ptpi.ReceiverNumber = MRPsld.docno
			AND ptpi.ReceiverItemNumber = MRPsld.docitem
			AND ptpi.LotNumber = MRPsld.snlotno
			AND ptpi.ExpirationDate = MRPsld.expirdate
			AND ptpi.LocationCode != MRPsld.vendlotno)
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno

WHEN MATCHED AND (ptpi.ReceivedDate != MRPsld.sndate
			OR ptpi.ReceiverNumber != MRPsld.docno
			OR ptpi.ReceiverItemNumber != MRPsld.docitem
			OR ptpi.LotNumber != MRPsld.snlotno
			OR ptpi.ExpirationDate != MRPsld.expirdate)
THEN 
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

Open in new window


So, if I remove the second 'WHEN MATCHED..." the merge will run. But I need the logic in this area.

Here's what I'm trying to do:

The first WHEN MATCHED makes sure that all of the important fields match before checking the location. If the location doesn't match I update only that.

The second WHEN MATCHED makes sure that the part number matches and if any of the other fields don't match (not looking at the location) then insert a new row.

Lastly, if the partno's don't match (WHEN NOT MATCH) then insert a new row.

Hope this all makes sense.

Thanks, Dennis
0
Comment
Question by:DennisStickles
  • 2
3 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39227716
Move all your conditions in the 1st MATCH condition to the JOIN condition.
MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON MRPsld.partno = ptpi.partno
AND ptpi.ReceivedDate = MRPsld.sndate
			AND ptpi.ReceiverNumber = MRPsld.docno
			AND ptpi.ReceiverItemNumber = MRPsld.docitem
			AND ptpi.LotNumber = MRPsld.snlotno
			AND ptpi.ExpirationDate = MRPsld.expirdate	
WHEN MATCHED 
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno
WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);	

Open in new window

0
 

Author Comment

by:DennisStickles
ID: 39228097
Wow! I guess I had been staring at this for too long. That makes perfect sense and once I added the test for the location to the first match it works like I wanted. Great job and thanks for your help!

Here is the final code:
MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON ptpi.partno = MRPsld.partno
AND ptpi.ReceivedDate = MRPsld.sndate
AND ptpi.ReceiverNumber = MRPsld.docno
AND ptpi.ReceiverItemNumber = MRPsld.docitem
AND ptpi.LotNumber = MRPsld.snlotno
AND ptpi.ExpirationDate = MRPsld.expirdate	
WHEN MATCHED AND ptpi.LocationCode != MRPsld.vendlotno
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno
WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);	

Open in new window


-Dennis
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39228227
Glad it helped.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question