Solved

MS SQL Syntax Merge

Posted on 2012-04-02
4
631 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
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 300 total points
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now