current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 1 down vote favorite
1

While I consider this to be a fairly simply query, apparently there is "Incorrect syntax near 'output'". Other online resources have not been helpful in debugging this problem.

What am I doing wrong here?

DECLARE @changes TABLE (client_id_copy INT, client_id INT);

UPDATE gmdev.contacts 
SET client_id_copy=a.client_id
FROM gmdev.profile a, gmdev.contacts b
output client_id_copy, inserted.client_id into @changes
WHERE a.custid=b.custid
and NOT(Client_ID_copy > '')
and b.custid in
(select custid from gmdev.profile where custtype='EZ2');

Edit:

The following suggestion DOES NOT WORK:

DECLARE @changes TABLE (client_id_copy INT, client_id INT);

UPDATE gmdev.contacts 
SET client_id_copy=a.client_id
OUTPUT client_id_copy, inserted.client_id into @changes
FROM gmdev.profile a, gmdev.contacts b
WHERE a.custid=b.custid
and NOT(Client_ID_copy > '')
and b.custid in
(select custid from gmdev.profile where custtype='EZ2');
share|improve this question
1  
What are you trying to do exactly..?? –  Hitesh Mistry Jun 7 '13 at 13:22
1  
just put the output clause one line above –  Serge Jun 7 '13 at 13:23
    
Serge is right. See BOL syntax definition: msdn.microsoft.com/en-us/library/ms177523.aspx –  MicSim Jun 7 '13 at 13:25
    
@HiteshMistry It's just a basic query to fix some data, but it has to spit out what it changed for logging purposes. –  Don Scott Jun 7 '13 at 14:14

4 Answers 4

up vote 3 down vote 
DECLARE @changes TABLE (client_id_copy INT, client_id INT);

UPDATE gmdev.contacts 
SET client_id_copy=a.client_id
output inserted.client_id_copy, inserted.client_id into @changes
FROM gmdev.profile a, gmdev.contacts b
WHERE a.custid=b.custid
and NOT(Client_ID_copy > '') -- Weird...
and b.custid in
(select custid from gmdev.profile where custtype='EZ2');
share|improve this answer
    
This did not solve the problem. I am still getting the "Incorrect syntax" error. Any other ideas? –  Don Scott Jun 7 '13 at 14:13
    
(+inserted) output inserted.client_id_copy, inserted.client_id into @changes ? –  Serge Jun 7 '13 at 14:17
    
No, that does not work, but it also isn't the desired result. I want to get the OLD value of client_id_copy as well as the value it has been replaced by. –  Don Scott Jun 7 '13 at 14:23
    
@don4of4 - just seen your most recent comment and updated my answer. If you want the OLD value, you have to reference deleted rather than inserted. –  Damien_The_Unbeliever Jun 7 '13 at 14:24
    
then it's the deleted table, I can't guess that ^^ –  Serge Jun 7 '13 at 14:24
up vote 3 down vote

We don't have your tables and data, so it's a bit tricky for us to debug any issues, but the following does compile and run:

create table contacts (client_id_copy int,custid int,client_id int)
create table profile(custid int,client_id int,custtype varchar(10))
DECLARE @changes TABLE (client_id_copy INT, client_id INT);

UPDATE contacts 
SET client_id_copy=a.client_id
OUTPUT deleted.client_id_copy,inserted.client_id into @changes
FROM profile a, contacts b
WHERE a.custid=b.custid
and NOT(Client_ID_copy > '')
and b.custid in
(select custid from profile where custtype='EZ2');

select * from @changes

As I say though, I don't know if its correct because we don't know what your tables look like (I've just made up some definitions). Every column listed in the OUTPUT clause has to include the relevant table name or alias (or inserted or deleted):

<column_name> ::=
   { DELETED | INSERTED | from_table_name } . { * | column_name }
   | $action

And note that { DELETED | INSERTED | from_table_name } isn't marked as optional, so that's why OUTPUT client_id_copy, doesn't work.

share|improve this answer
up vote 0 down vote

A simplified example:

CREATE TABLE #contacts(client_id_copy INT NULL, custid INT NULL);
CREATE TABLE #profile(client_id INT NULL, custid INT NULL);

DECLARE @changes TABLE (client_id_copy INT, client_id INT);

UPDATE 
    #contacts 
SET 
    client_id_copy=a.client_id
OUTPUT 
    inserted.client_id_copy AS client_id_copy, 
    a.client_id AS client_id
    INTO @changes
FROM 
    #contacts AS b
    INNER JOIN #profile AS a ON
        a.custid=b.custid

DROP TABLE #contacts;
DROP TABLE #profile;
share|improve this answer
up vote 0 down vote accepted

In come cases, lazy system administrators may not upgrade to an up-to-date version of SQL.

First, make sure the OUTPUT keyword is supported by running Select @@version; This will return a cell like so:

Microsoft SQL Server  2000 - 8.00.2282 (Intel X86) 
Dec 30 2008 02:22:41 
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

If the result is older than Microsoft SQL Server 2005 then OUTPUT is not supported!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.