asked on

How can i use insert and update together by merging two tables?

Hi;

My question is related to merging. I have 2 tables. i guess what i need Merging including insert (when new) or Update (when existing)

1) zDeliveryItems
2) DeliveryItems

if an item is existed in zDeliveryitems but not existed in deliveryitems by using :

c.SapContractNumber = z.SapContractNumber
AND c.MaterialNumber = z.MaterialNumber
AND c.SapDeliveryNumber = z.SapDeliveryNumber
AND c.Batch = z.Batch

this criteria . Below sql query should run. if SapContractNumber,MaterialNumber,SapDeliveryNumber,Batch Numbers aren't matched between ZDeliveryitems and Deliveryitems, Processing never update Quantity and other field. My original query is working only for new items (insert only)

But; i wanna add new items but i wanna update my old item's
[ContractDescription]
,[ExpiryDate]
,[MaterialDescription]
       ,[NetPriceTotal]
              ,[NetPriceUnit]
              ,[Position]
              ,[Quantity]
,[SapOrderPosition]
              ,[Unit]
,[WebShopOrderNumber]

My ORIGINAL SQL QUERY :

CREATE PROCEDURE [dbo].[InsertDeliveryItemsV2]    
  
AS    
BEGIN    
     
   
 INSERT INTO [dbo].[DeliveryItems]    
           ([Batch]    
           ,[ContractDescription]    
           ,[ExpiryDate]    
           ,[MaterialDescription]    
           ,[MaterialNumber]    
     ,[NetPriceTotal]    
     ,[NetPriceUnit]    
     ,[Position]    
     ,[Quantity]    
     ,[SapContractNumber]    
     ,[SapDeliveryNumber]    
     ,[SapOrderPosition]    
     ,[Unit]    
     ,[WebShopOrderNumber]    
     )    
      SELECT Batch, ContractDescription, ExpiryDate, MaterialDescription, MaterialNumber, NetPriceTotal, NetPriceUnit,   
                            Position, Quantity, SapContractNumber, SapDeliveryNumber, SapOrderPosition, Unit, WebShopOrderNumber   
                            FROM zDeliveryItems z  
                            WHERE NOT EXISTS   
                            (SELECT * FROM DeliveryItems c   
                            WHERE c.SapContractNumber = z.SapContractNumber   
                            AND c.MaterialNumber = z.MaterialNumber  
                            AND c.SapDeliveryNumber = z.SapDeliveryNumber  
                            AND c.Batch = z.Batch)  
  
END

Open in new window

MY DESIRED QUERY :

CREATE PROCEDURE [dbo].[InsertDeliveryItemsV2]    
  
AS    
BEGIN    
     
   
 INSERT INTO [dbo].[DeliveryItems]    
           ([Batch]    
           ,[ContractDescription]    
           ,[ExpiryDate]    
           ,[MaterialDescription]    
           ,[MaterialNumber]    
     ,[NetPriceTotal]    
     ,[NetPriceUnit]    
     ,[Position]    
     ,[Quantity]    
     ,[SapContractNumber]    
     ,[SapDeliveryNumber]    
     ,[SapOrderPosition]    
     ,[Unit]    
     ,[WebShopOrderNumber]    
     )    
      SELECT Batch, ContractDescription, ExpiryDate, MaterialDescription, MaterialNumber, NetPriceTotal, NetPriceUnit,   
                            Position, Quantity, SapContractNumber, SapDeliveryNumber, SapOrderPosition, Unit, WebShopOrderNumber   
                            FROM zDeliveryItems z  
                            WHERE NOT EXISTS   
                            (SELECT * FROM DeliveryItems c   
                            WHERE c.SapContractNumber = z.SapContractNumber   
                            AND c.MaterialNumber = z.MaterialNumber  
                            AND c.SapDeliveryNumber = z.SapDeliveryNumber  
                            AND c.Batch = z.Batch)  
  if (Exists        (SELECT * FROM DeliveryItems c   
                            WHERE c.SapContractNumber = z.SapContractNumber   
                            AND c.MaterialNumber = z.MaterialNumber  
                            AND c.SapDeliveryNumber = z.SapDeliveryNumber  
                            AND c.Batch = z.Batch)  )
BEGIN
Update
   [ContractDescription]    
           ,[ExpiryDate]    
           ,[MaterialDescription]    
    	   ,[NetPriceTotal]    
		   ,[NetPriceUnit]    
		   ,[Position]    
		   ,[Quantity]    
           ,[SapOrderPosition]    
		   ,[Unit]    
           ,[WebShopOrderNumber]    
END
END

Open in new window

Guy Hengel [angelIII / a3]

you want to MERGE using the MERGE statement:
https://msdn.microsoft.com/en-us/library/bb510625.aspx

Vitor Montalvão

If don't understand the logic.
You first INSERT and then check IF EXISTS and if so, you UPDATE the inserted records?
That's not MERGE. MERGE is: - UPDATE if EXISTS, INSERT if not.

programmerist 1983

ASKER

Hi Vitor;

Second one is correct :

That's not MERGE. MERGE is: - UPDATE if EXISTS, INSERT if not. -> i want this.

programmerist 1983

ASKER

Yes. i wanna merge. if exist , i wanna update only ( ,[ExpiryDate]
,[MaterialDescription]
,[NetPriceTotal]
,[NetPriceUnit]
,[Position]
,[Quantity]
,[SapOrderPosition]
,[Unit]
,[WebShopOrderNumber])

if does not exist ,

insert new items...

Vitor Montalvão

I'm a little bit septic about the performance of the MERGE command but since I already heard some Experts affirming that's ok then you can try the following:

ALTER PROCEDURE [dbo].[InsertDeliveryItemsV2]    
  
AS    
BEGIN    
	MERGE DeliveryItems AS TARGET
	USING (SELECT 1 
		FROM DeliveryItems c   
			INNER JOIN zDeliveryItems z ON c.SapContractNumber = z.SapContractNumber   
						AND c.MaterialNumber = z.MaterialNumber  
						AND c.SapDeliveryNumber = z.SapDeliveryNumber  
						AND c.Batch = z.Batch)
	WHEN MATCHED THEN
		UPDATE c
		SET c.[ContractDescription] = z.[ContractDescription]
			,c.[ExpiryDate] = z.[ExpiryDate]
			,c.[MaterialDescription] = z.[MaterialDescription]
    		,c.[NetPriceTotal] = z.[NetPriceTotal]
			,c.[NetPriceUnit] = z.[NetPriceUnit]
			,c.[Position] = z.[Position]
			,c.[Quantity] = z.[Quantity]
			,c.[SapOrderPosition] = z.[SapOrderPosition]
			,c.[Unit] = z.[Unit]
			,c.[WebShopOrderNumber] = z.[WebShopOrderNumber]
		FROM DeliveryItems c   
			INNER JOIN zDeliveryItems z ON c.SapContractNumber = z.SapContractNumber   
						AND c.MaterialNumber = z.MaterialNumber  
						AND c.SapDeliveryNumber = z.SapDeliveryNumber  
						AND c.Batch = z.Batch

	WHEN NOT MATCHED THEN
		INSERT INTO [dbo].[DeliveryItems](
			[Batch]    
			,[ContractDescription]    
			,[ExpiryDate]    
			,[MaterialDescription]    
			,[MaterialNumber]    
			,[NetPriceTotal]    
			,[NetPriceUnit]    
			,[Position]    
			,[Quantity]    
			,[SapContractNumber]    
			,[SapDeliveryNumber]    
			,[SapOrderPosition]    
			,[Unit]    
			,[WebShopOrderNumber])    
		SELECT Batch, ContractDescription, ExpiryDate, MaterialDescription, MaterialNumber, NetPriceTotal, NetPriceUnit,   
			Position, Quantity, SapContractNumber, SapDeliveryNumber, SapOrderPosition, Unit, WebShopOrderNumber   
		FROM zDeliveryItems z  
		WHERE NOT EXISTS   
			(SELECT 1 
			FROM DeliveryItems c   
			WHERE c.SapContractNumber = z.SapContractNumber   
				AND c.MaterialNumber = z.MaterialNumber  
				AND c.SapDeliveryNumber = z.SapDeliveryNumber  
				AND c.Batch = z.Batch)  
END

Open in new window

Vitor Montalvão

If you want, use this V3 option, that doesn't use the MERGE command, to state the performance difference:

CREATE PROCEDURE [dbo].[InsertDeliveryItemsV3]    
AS		
	UPDATE c
	SET c.[ContractDescription] = z.[ContractDescription]
		,c.[ExpiryDate] = z.[ExpiryDate]
		,c.[MaterialDescription] = z.[MaterialDescription]
    	,c.[NetPriceTotal] = z.[NetPriceTotal]
		,c.[NetPriceUnit] = z.[NetPriceUnit]
		,c.[Position] = z.[Position]
		,c.[Quantity] = z.[Quantity]
		,c.[SapOrderPosition] = z.[SapOrderPosition]
		,c.[Unit] = z.[Unit]
		,c.[WebShopOrderNumber] = z.[WebShopOrderNumber]
	FROM DeliveryItems c   
		INNER JOIN zDeliveryItems z ON c.SapContractNumber = z.SapContractNumber   
						AND c.MaterialNumber = z.MaterialNumber  
						AND c.SapDeliveryNumber = z.SapDeliveryNumber  
						AND c.Batch = z.Batch

	IF @@ROWCOUNT = 0 THEN
		INSERT INTO [dbo].[DeliveryItems](
			[Batch]    
			,[ContractDescription]    
			,[ExpiryDate]    
			,[MaterialDescription]    
			,[MaterialNumber]    
			,[NetPriceTotal]    
			,[NetPriceUnit]    
			,[Position]    
			,[Quantity]    
			,[SapContractNumber]    
			,[SapDeliveryNumber]    
			,[SapOrderPosition]    
			,[Unit]    
			,[WebShopOrderNumber])    
		SELECT Batch, ContractDescription, ExpiryDate, MaterialDescription, MaterialNumber, NetPriceTotal, NetPriceUnit,   
			Position, Quantity, SapContractNumber, SapDeliveryNumber, SapOrderPosition, Unit, WebShopOrderNumber   
		FROM zDeliveryItems z  
		WHERE NOT EXISTS   
			(SELECT 1 
			FROM DeliveryItems c   
			WHERE c.SapContractNumber = z.SapContractNumber   
				AND c.MaterialNumber = z.MaterialNumber  
				AND c.SapDeliveryNumber = z.SapDeliveryNumber  
				AND c.Batch = z.Batch)  
GO

Open in new window

programmerist 1983

ASKER

Hi Vitor;

When i try your first advise . i puzzled this error :

Msg 156, Level 15, State 1, Procedure InsertDeliveryItemsV2, Line 12 [Batch Start Line 0]
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure InsertDeliveryItemsV2, Line 30 [Batch Start Line 0]
Incorrect syntax near the keyword 'WHEN'.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

programmerist, a feedback will be appreciated.
Cheers