SQL Output Syntax

Question

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');

Serge is right. See BOL syntax definition: msdn.microsoft.com/en-us/library/ms177523.aspx — MicSim
– MicSim, Commented Jun 7, 2013 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
– Don Scott, Commented Jun 7, 2013 at 14:14

Serge · Accepted Answer · 2013-06-07 14:17:17Z

3

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');

edited Jun 7, 2013 at 14:17

answered Jun 7, 2013 at 13:22

Serge

6,7425 gold badges33 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Don Scott Over a year ago

This did not solve the problem. I am still getting the "Incorrect syntax" error. Any other ideas?

Serge Over a year ago

(+inserted) output inserted.client_id_copy, inserted.client_id into @changes ?

Don Scott Over a year ago

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.

Damien_The_Unbeliever Over a year ago

@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.

Serge Over a year ago

then it's the deleted table, I can't guess that ^^

Damien_The_Unbeliever · Accepted Answer · 2013-06-07 14:20:23Z

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.

TT. · Accepted Answer · 2013-06-07 14:29:32Z

1

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;

answered Jun 7, 2013 at 14:29

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Comments

Don Scott · Accepted Answer · 2013-06-07 14:38:21Z

0

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!

answered Jun 7, 2013 at 14:38

Don Scott

3,5071 gold badge30 silver badges42 bronze badges

Collectives™ on Stack Overflow

SQL Output Syntax

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related