Insert - need to select value for output but not insert

Asked Aug 04 '14 at 17:44

Active Aug 04 '14 at 17:44

Viewed 52 times

Working on an import script for SQL Server, and using the output answer (by Andy Irving) at How to insert multiple records and get the identity value? the script is working almost perfectly. The one problem is that the following syntax doesn't work:

INSERT INTO [Table1] (V1a)
OUTPUT 'Test1', V2a, 'Test2', Inserted.Id INTO @output
SELECT
   Distinct(V1b)
FROM
   Table2
WHERE (stuff matches this)

What I am looking to do here, is fill V2a with a corresponding V2b value from Table2, purely for output purposes (the output will be inserted into another table). Currently, if I select V1b, V2b from Table2, I get an error about an inequal number of values between the select and the insert statements. Is it possible to include a value in the select statement purely for the output statement without the insert statement throwing an error?

edited May 23 '17 at 12:05

Community

asked Aug 04 '14 at 17:44

Bardicer

1,405
4
26
43

OUTPUT can only be used to output data that is being inserted, from what I've read, so you would have to insert it into Table1 in order to include it in @Output. – Tab Alleman Aug 04 '14 at 18:17
That's what I thought. Thanks for the confirmation. – Bardicer Aug 04 '14 at 18:41
@TabAlleman you can do this with `merge` as in the duplicate. – Martin Smith Aug 04 '14 at 18:50

SQL Server, Select/Output/Insert - need to select value for output but not insert

0 Answers0