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


Monday, July 24, 2006

stored proc to new table

SQL Apprentice Question
what's the easiest way to get the results of a stored procedure into a new
table?


thanks,


Celko Answers

>> what's the easiest way to get the results of a stored procedure into a new table? <<


Quick answer: put an INSERT INTO statement in the body of the stored
procedure.

Better answer: you should never be creating table on the fly. You can
put the SELECT into a VIEW and call it that way. Based on cleaning up
non-RDBMS designs written in SQL, I will guess that you are mimicking a
scratch tape.


If SQL were a 1950's tape file system language when we did not have
much disk storage and it costs a fortune. One **procedural** step in
the procedure would write a result to a new tape, then the next step
would do more work with it. I will also bet that you have cursors in
your code.


Once you learn to actually think in a declarative language like SQL,
you will not even consider such kludges. You will ask for what you
want in one statement.

No comments: