create table on the fly with OUTPUT INTO, not allowed?

Question

We can do SELECT * INTO #TmpTbl FROM Tbl.

So I was thinking we can do something similar with OUTPUT, but it doesn't look like you can do that. All the examples I found do it where you first create the temp table or table variable and OUTPUT INTO it.

Am I correct that you can't create temp tables on the fly with OUTPUT INTO?

Answer 1

You're correct. The table must exist before can be used with OUTPUT INTO.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

What is scenario where you are trying to use the OUTPUT clause?

Can you post a sample query?

BTW, you can create table on-the-fly, by adding an extra SELECT statement with "WHERE 1=2" clause before the query containing OUTPUT clause.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 3

OK, thanks Naomi for verifying my initial finding.

And manub, I understand what you are suggesting. I am just trying to avoid manaully creating that table. But like you said, I can run a select * into where 1=2 to create the empty table before I do the sql where I want to use OUTPUT.

Answer 4

Check your scenario you can use Merge:

1) http://www.robersonferreira.com.br/merge_parte1/

2) http://www.robersonferreira.com.br/merge_parte2/

3) http://www.robersonferreira.com.br/merge_parte3/

---

Roberson Ferreira - Database Developer
Acesse: www.robersonferreira.com.br
Email: [e-mail]

Se esta sugestão for útil, por favor, classifique-a como útil.
Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.