Sql Merge Output

Question

Sql Merge Output

xxx-593414

SSC Eights!

Points: 846
More actions
January 29, 2013 at 4:25 am

#273521

If I use a SQL Merge with an Output Clause, If I dont specify an INTO, it works fine
However, If I use an INTO clause , it errors with a 'Column Name or number of supplied values does not match table...
I can seem to get the Column Names correct in the output table
1. Does it matter what they are called
2. Do they have to be same as source
3. Do they have to be same as source with an old_ and a new_ prefixed
4. Does the Type matter
I just cannot seem to get the column names correct

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

Post complete example (TSQL script).

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

e4d4 SSCertifiable Points: 5777 More actions · Answer 2

gerard-593414 (1/29/2013)
If I use a SQL Merge with an Output Clause, If I dont specify an INTO, it works fine
However, If I use an INTO clause , it errors with a 'Column Name or number of supplied values does not match table...
I can seem to get the Column Names correct in the output table
1. Does it matter what they are called
2. Do they have to be same as source
3. Do they have to be same as source with an old_ and a new_ prefixed
4. Does the Type matter
I just cannot seem to get the column names correct

Here you have example with MERGE and OUTPUT http://technet.microsoft.com/en-us/library/bb510625.aspx example "B. Using MERGE to perform UPDATE and DELETE operations on a table in a single statement".

xxx-593414 SSC Eights! Points: 846 More actions · Answer 3

Hi Vedran.

The problem is that I have tried numerous options now , and none is working.

I've tried removing columns, adding columns etc, to no avail

e.g.

MERGE TA AS T

USING TB AS S

ON (T.F1 = S.F1)

WHEN MATCHED THEN

UPDATE SET T.F2 = S.F2. T.F3 = S.F3

OUTPUT F2,F3 INTO AuditTable

(IF i LEAVE OUT THE into AuditTable, it runs fine)

So my qusetion is , what field names (and types) do I need in AuditTable

HowardW One Orange Chip Points: 29109 More actions · Answer 4

gerard-593414 (1/29/2013)
(IF i LEAVE OUT THE into AuditTable, it runs fine)
So my qusetion is , what field names (and types) do I need in AuditTable

The same number as in your output list (excluding identities/computed columns etc. that are not valid for the Insert).

As pointed out in the documentation referred to above, you can also use a MERGE statement with an output clause like a derived table and use this sort of syntax, which is more flexible in specifying insert columns/order:

INSERT INTO AuditTable (F2,F3)

select F2, F3 from (

MERGE TA AS T

USING TB AS S

ON (T.F1 = S.F1)

WHEN MATCHED THEN

UPDATE SET T.F2 = S.F2. T.F3 = S.F3

OUTPUT inserted.F2,inserted.F3 ) as Changes (F2, F3)

I'd also explicitly qualify your OUTPUT clause with inserted or deleted.