How to use OUTPUT clause with SELECT/INTO statement

Question

I have the following query

SELECT 'Luke' AS [Jedi_names], 'Skywalker' AS [Jedi_surname]
INTO #Jedi

which CREATE the temporary table #Jedi and INSERT the data inside it.

I would like to use OUTPUT to show the data I am saving inside the table but I can't understand how to use the OUTPUT clause without getting an error message

"Incorrect syntax near 'output'."

In an INSERT INTO query I would write the following

INSERT INTO #Jedi([Jedi_names],[Jedi_surname])
OUTPUT INSERTED.*
SELECT 'Luke' AS [Jedi_names], 'Skywalker' AS [Jedi_surname]

but this just INSERT the line if the table has been already created..

Is it possibile to use the OUTPUT clause in the first query?

Answer 1

You can't use the output clause in a select statement. It's only applicable in insert, update, delete and merge.

An alternative option would be to do it in two parts: First, create the temporary table with no records:

SELECT 'Luke' AS [Jedi_names], 'Skywalker' AS [Jedi_surname]
INTO #Jedi 
WHERE 1=0

Then, insert the records into the temporary table using insert...output...select:

INSERT INTO #Jedi
OUTPUT INSERTED.*
SELECT 'Luke' AS [Jedi_names], 'Skywalker' AS [Jedi_surname]

Or simply use select into and then select:

SELECT 'Luke' AS [Jedi_names], 'Skywalker' AS [Jedi_surname]
INTO #Jedi;

SELECT  [Jedi_names], [Jedi_surname]
FROM #Jedi;