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

Answer 1

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 2

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.