MS SQL Syntax Merge

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?
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick TallaricoFSEP Systems AnalystCommented:
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
rwheeler23Author Commented:
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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.