Friday, March 30, 2007

How to create a table from a select statement

Ready to make a big update, before you do make a backup of that data into a temp table with a select. This save me few times.
create table tmp_eric as 
( select * from ... where ... )

Each columns returned from the select will become the columns of the new table tmp_eric.

For MSSQL, you need to first create the table then you can use the insert

create table tmp_eric (col1, INTEGER PRIMARY KEY, col2...)

insert into tmp_eric (col1, col2,...)
select col1, col2,... from eric

You can also use:

select * into tmp_eric from eric

No comments: