Copying tables..
Hi Everyone.
I'm copying a table something like the loop below, and I was wandering if
there is any alternative to manually specifying all the fields? I know I could
do it with a single 'select into' statement, but that results in an
unreasonably large transaction, hence the loop. What I'd like is a generic
table copy routine. I see that I could do it in C, but I'd rather keep to SPL
if I can?
FOREACH cur_su WITH HOLD FOR
SELECT spa,spb INTO fa,fb FROM tab0 INSERT into tab1 (spa,spb) values (fa,fb);END FOREACH;
Sorry if this is obvious everyone - SQL newbie here.
Regards,
Tristan
Create your new table as a RAW (unlogged) table, then use the SELECT INTO
statement. After the table loads you can change it to a STANDARD (logged)
table. That will avoid a long transaction. Be sure to run a backup when you've
finished, though.
--EEM
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>Tristan Ball
>Sent: Thursday, March 03, 2011 3:24 PM
>To: ids@iiug.org
>Subject: Copying tables.. [22972]
>
>Hi Everyone.
>
>I'm copying a table something like the loop below, and I was wandering
>if there is any alternative to manually specifying all the fields? I
>know I could do it with a single 'select into' statement, but that
>results in an unreasonably large transaction, hence the loop. What I'd
>like is a generic table copy routine. I see that I could do it in C, but
>I'd rather keep to SPL if I can?
>
>FOREACH cur_su WITH HOLD FOR
>
>SELECT spa,spb INTO fa,fb FROM tab0 INSERT into tab1 (spa,spb) values
>(fa,fb); END FOREACH;>
>Sorry if this is obvious everyone - SQL newbie here.
>
>Regards,
>
>Tristan
>
>
>************************************************************************
>*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
Download my package, utils2_ak, from the IIUG Software Repository. Included
in the package is the generic table copy utility you are looking for. It is
called dbcopy.ec.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Mar 3, 2011 at 4:24 PM, Tristan Ball <tristanb@pronto.com.au> wrote:
> Hi Everyone.
>
> I'm copying a table something like the loop below, and I was wandering if
> there is any alternative to manually specifying all the fields? I know I
> could
> do it with a single 'select into' statement, but that results in an
> unreasonably large transaction, hence the loop. What I'd like is a generic
> table copy routine. I see that I could do it in C, but I'd rather keep to
> SPL
> if I can?
>
> FOREACH cur_su WITH HOLD FOR
>
> SELECT spa,spb INTO fa,fb FROM tab0 INSERT into tab1 (spa,spb) values
> (fa,fb);> END FOREACH;
>
> Sorry if this is obvious everyone - SQL newbie here.
>
> Regards,
>
> Tristan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3010ed9960b776049d9afe0e