Quickly moving data with a select statement

There's a few really useful tricks with a select statement I'd like to share. Primarily this is with oracle but it works on other databases as well.

1. You can always quickly create a table using a select statement, it won't, however, create the indexes on the new table
   

   create table zac.user_role_bak_apr08
   as select * from zac.user_role;



2. You can also use a select with an insert statement.
   
   a) if the table has an identical column list
   

   insert into zac.user_role_bak_apr08
   select * from zac.user_role;

   
   b) otherwise you should specify the column names
   

   insert into zac.user_role_bak_apr08
   (user_id, role_id, lastupdated)
   select user_id, role_id, datecreated
   from zac.user_role_bak_apr08;

Remember you have to commit when use the insert approach, but not with the
create table approach.