SQL output syntax

Question

Although I think this is a fairly simple request, apparently there is a “Wrong syntax next to“ output. ”Other online resources did not help 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 sentence 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'); 

Answer 1

In the following cases, lazy system administrators may not upgrade to an updated version of SQL .

First, make sure the OUTPUT keyword is supported by running Select @@version; . This will return the cell as follows:

 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 not supported!

Answer 2

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

Answer 3

We do not have your tables and data, so it’s difficult for us to debug any problems, but the following compiles and runs:

 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 said, I don’t know if this is right, because we don’t know what your tables look like (I just made some definitions). Each column specified in the OUTPUT section must contain the corresponding table name or alias (either inserted or deleted ):

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

Please note that { DELETED | INSERTED | from_table_name } { DELETED | INSERTED | from_table_name } { DELETED | INSERTED | from_table_name } not marked as optional, therefore OUTPUT client_id_copy, does not work.

Answer 4

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;