Thursday, May 01, 2008

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.

No comments: