none
MERGE Statement

    Question

  • Hi All

    I have 3 tables

    tbl_customer_orders - current order pending
    tbl_inventory_orders - the company making orders with its suppliers to replenish the existing stock
    tbl_inventory - the current list of invesntory i.e. the product, quantity etc...

    The logic is that when a Customer makes an order it will be logged into the tbl_customer_orders table which happening at the moment. I then want a merge statement to insert a row into the tbl_inventory_orders table to replenish the stock. BUT I also want to update the tbl_inventory tables [quantity] column to deduct the quantity ordered in the tbl_customer_orders table.

    Question

    How can I insert rows into the tbl_inventory_orders table using the MERGE statement and also Update the tbl_inventory table to deduct the quantity of the products ordered by the customer.

    CREATE TABLE [dbo].[tbl_customer_orders]
    (
    	[order_id] [int] IDENTITY(1,1) NOT NULL,
    	[cus_id] [int] NOT NULL,
    	[prod_id] [int] NOT NULL,
    	[quantity] [smallint] NOT NULL,
    	CONSTRAINT [PK__tbl_customer_orders] PRIMARY KEY CLUSTERED ([order_id])
    )
    GO
    
    
    CREATE TABLE [dbo].[tbl_inventory]
    (
    	[prod_id] [int] NOT NULL,
    	[quantity] [int] NOT NULL,
    	[prod_desc] [varchar](1000) NOT NULL,
    	CONSTRAINT [PK__tbl_inventory] PRIMARY KEY CLUSTERED ([prod_id])
    )
    GO
    
    
    CREATE TABLE [dbo].[tbl_inventory_orders]
    (
    	[order_id] [int] IDENTITY(1,1) NOT NULL,
    	[prod_id] [int] NOT NULL,
    	[quantity] [smallint] NOT NULL,
    	[cus_order_id] [int] NOT NULL,
    	CONSTRAINT [PK__tbl_inventory_orders] PRIMARY KEY CLUSTERED ([order_id])
    )

    I am able to insert into the tbl_invesntory_orders table but then I need to update the tbl_inventory

    merge dbo.tbl_inventory_orders as t
    using dbo.tbl_customer_orders as s
    on t.cus_order_id = s.order_id
    when not matched then
    	insert ([prod_id], [quantity], [cus_order_id])
    	values(s.prod_id, s.quantity, s.order_id);

    Thanks


    • Edited by shamas2121 Thursday, February 20, 2014 4:13 PM
    Thursday, February 20, 2014 4:12 PM

Answers

  • I don't think you will be able to do insert/update operation on a table that's not participating in the merge. One option would be to use a trigger on tbl_inventory_orders to update the inventory table.

    What you could probably do is use the output clause to do the insert into the inventory table as in this example

    CREATE TABLE [USER]
    (userid INT, username VARCHAR(50), locationid INT)
    
    CREATE TABLE LOCATION
    (locationid INT, name VARCHAR(50))
    INSERT INTO location VALUES(1,'Delhi')
    INSERT INTO location VALUES(2,'Noida')
    
    
    INSERT INTO [USER] VALUES(1,'Sandeep',1)
    
    create table result(UserName varchar(50), NewLocaionId Int)
    
    
    UPDATE [USER] SET locationid=2
    OUTPUT  INSERTED.UserName, INSERTED.locationid INTO result
    WHERE userid=1
    
    SELECT * FROM result


    Satheesh
    My Blog | How to ask questions in technical forum



    Thursday, February 20, 2014 4:45 PM

All replies

  • Just use a second query to UPDATE the inventory table based on the data in customer_orders.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 20, 2014 4:15 PM
  • I don't think you will be able to do insert/update operation on a table that's not participating in the merge. One option would be to use a trigger on tbl_inventory_orders to update the inventory table.

    What you could probably do is use the output clause to do the insert into the inventory table as in this example

    CREATE TABLE [USER]
    (userid INT, username VARCHAR(50), locationid INT)
    
    CREATE TABLE LOCATION
    (locationid INT, name VARCHAR(50))
    INSERT INTO location VALUES(1,'Delhi')
    INSERT INTO location VALUES(2,'Noida')
    
    
    INSERT INTO [USER] VALUES(1,'Sandeep',1)
    
    create table result(UserName varchar(50), NewLocaionId Int)
    
    
    UPDATE [USER] SET locationid=2
    OUTPUT  INSERTED.UserName, INSERTED.locationid INTO result
    WHERE userid=1
    
    SELECT * FROM result


    Satheesh
    My Blog | How to ask questions in technical forum



    Thursday, February 20, 2014 4:45 PM
  • I didn't look too close, but would you be able to use composable DML in this case?

    http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 20, 2014 5:05 PM
  • Merge is typically used to sync source and target. In a single merge statement you can either UPSERT or DELETE data from either source or destination. If you want to update the third table based on some conditions, you can output the actions into a temp table and use the data to update the data in third table.

    http://technet.microsoft.com/en-us/library/ms177564.aspx

    You can refer to section K

    Thursday, February 20, 2014 6:12 PM