Creating a table as statement 1

[perlriffic]

I need to create a temp table that has the same structure and some of the same values that are in another table (I'm going to do an update of the production table and want a copy of the original data).
I tried something like this, but it bombed

create temp table work_area as
select * from orig_table
where field = 'value';

I have done this in Sybase and Oracle and it worked. I can't get it to work in Informix. Am I missing something?

 

[vpshriyan]

Hi,

Thre are 2 ways:

-- create a empty temp table
select * from otab where rowid=0 into temp ttab with no log;
-- and then insert the row from the original table
insert into ttab select * from otab where col1='23' ;

--or in one shot:
select * from otab where col1='23' into temp ttab with no log;

However, you must note that the data store in a temp table is temparoty in nature, i.e, you will lose data once you close your sql session. If you need data in permanent status, you better store it other base table.

Regards,
Shriyan

 

[perlriffic]

Thanks Shriyan,
I can't create a regular table because my group doesn't have the permissions. I will have to use a temp table and leave my session open while the testing completes. I could just unload the table to a file...
I discovered the lack of priveleges yesterday, however I was sidetracked trying to figure out why my create statement failed. I couldn't get the create to work in a single statement, however I had success with the two step method.
Thanks for all your help )

Perlriffic