My Favorite Sql "Feature"

I just thought I'd share my two favorite Sql features with everyone. They come in very handy when moving data around between tables. The first is a nifty way of creating a table and inserting into the table in one easy step.

Create table fooTable as

select a.key, a.first, a.last, b.street, b.zip

from aTable a,

anotherTable b

where

a.key = b.key

and a.other = b.other

The above statement will actually create a table with the columns key, first, last, street, and zip and also insert all rows returned by the select query. Now for the second useful statement. Suppose you already have a table created, and you need to insert a whole bunch of data. Instead of writing a pesky pl-sql procedure/function, just use an sql select instead.

insert into fooTable a (
a.key, a.first, a.last, b.street, b.zip)
select b.key, b.first, b.last, c.street, c.zip
from aTable b,
anotherTable c
where a.key = b.key
and a.other = b.other

And now you can move your database data around with ease. I know that many of you probably already knew all of this, but hey...I thought it was cool.