SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, June 05, 2006

SELECT INTO with a table variable

SQL Apprentice Question
Is is possible to do something like this:

SELECT A,B,C INTO @TableVariable FROM @AnotherTableVariable?

I try it and I get syntax errors and I am not sure if it is just something I
am doing wrong or if it is something that is not supported. I know when I use
JOIN I need to specify an alias inseated of the full table variable name.
Maybe there is something similar with SELECT * INTO.

Thank you.


Celko Answers
>> Is is possible to do something like this: <<

You tried it and got an error, so the answer is No :).

But a better question is why do you want to have two identical tables
in a schema? Why are you seeking to write code with poor cohesion, via
the proprietary table variables (I.e. your statement has no idea what
it is operating on until an unknown future user makes this design
decision for you on the fly -- could squids or automobiles, depending
on @TableVariable!)

Kevin, the questions you are posting show that you have not done your
homework and the best you can hope for is a pile of awful kludges from
people in the newsgroup who do not have the time to educated you
properly. By analogy, if this were a furniture makers newsgroup, you
are asking "what is the best kind of rock to smash screws into wood?"
The newsgroup answer will "Granite!", but the right answer is "you need
to learn about screwdrivers and the right kinds of screws for
particular job".

No comments: