Solved

MS SQL Syntax Merge

Posted on 2012-04-02
4
637 Views
Last Modified: 2012-06-21
I wrote a merge query following this example, I get the same error message as this sample gives me. The error is
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.

This sample is taken directly off the MS web site.
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';

Can someone please identify the error?
0
Comment
Question by:rwheeler23
4 Comments
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 100 total points
ID: 37798642
f you add the line:
use Production

and then remove the references to the database in the rest of the code, this may resolve the issue, or give you more insight.
I thought that MS Sql required the username in the table reference if the database was provided.  Or else a double dot.

Production.ProductInventory
becomes
Production.dbo.ProductInventory  (or) Production.(user).ProductInventory
or
Production..ProductInventory
0
 

Author Comment

by:rwheeler23
ID: 37798757
Adventureworks is the database and Production is the schema. I had tried in my example with and without the databasename and schema and it still does not work.

INSERT INTO Adventureworks.Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Adventureworks.Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 37801395
Try adding the column names to the INSERTed table:


INSERT INTO Adventureworks.Production.UpdatedInventory (ProductID, ..., ..., ...)  --<<-- ***
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Adventureworks.Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 100 total points
ID: 37805500
If you can't situate a problem in a SQL (both syntax as result) it can start with the smalest executional part and build it up until it goes wrong. With complex queries for example commenting out subqueries or joins

Here you could have 6 stages to test
1) select    group by  
2) merge with that select and the first matched
3) additional matched
4) also output-part
5) select from  (merge)
6) insert into

You can do this in your sql perfectly by just selecting the text and execute the selected query.  In other cases this is not always possible.
When testing an update-statement it also good to think about putting BEGIN TRAN/ROLLBACK TRAN around your test-sql so it will also MERGE the second run (or mistake is not commited). This is naturaly not combinable with the previous 'execute selected query'.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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