Solved

MS SQL Syntax Merge

Posted on 2012-04-02
4
636 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 50
SQL Server - executing an agent job from a stored proc 2 25
MS SQL 2008 Divide by zero error encountered. Error 3 45
Sql query with where clause 2 34
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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