Page 1 of 1

Backup DB2 table in "INSERT" instruction.

PostPosted: Thu Jul 15, 2010 3:36 pm
by leonenapoletano
Hello,
before all, sorry for my English, i'm Italian.

I was looking for a way to create, from the contents of a table, a "SPUFI" file that contains all "INSERT INTO" instructions, for each row in the table.

EXAMPLE:-----------------------------------------------------------------------------------------
start situation
SELECT * FROM FROM DB2.T3IDRIF

                                             
      COD  FL                      DT 
      BK   VAL    PROGVAL          RIF
 --------  -----  -------  -----------
      104  S            0     20091231
      104  N            0     20101231



result expected

INSERT INTO DB2.T3IDRIF
 ( COD_BK
 , FL_VAL
 , PROGVAL
 , DT_RIF )
VALUES
 ( 104
 , 'S'
 , 0
 , 20091231 );

INSERT INTO DB2.T3IDRIF
 ( COD_BK
 , FL_VAL
 , PROGVAL
 , DT_RIF )
VALUES
 ( 104
 , 'N'
 , 0
 , 20101231 );


END-EXAMPLE ----------------------------------------------------------------------------------

The target is naturally to create a backup file, that works for different environment.

Thank you.

Massimo

Re: Backup DB2 table in "INSERT" instruction.

PostPosted: Thu Jul 15, 2010 5:02 pm
by GuyC
something like
select 'INSERT INTO TAB1 (' !! char(col_bk) !!  ',''' !! flcal !! ''',' !! char(DT_RIF) !! ');'
from tab1

would do the trick.
But why don't you unload the table to a CSV-file ? most DBMSs support a kind of import/export from CSV.

Re: Backup DB2 table in "INSERT" instruction.

PostPosted: Fri Jul 16, 2010 8:54 pm
by leonenapoletano
Thanks for the answer, it is the result I expected.

The result was:

INSERT INTO DB2.T3IDRIF ( 00000000000104. , 'S' , 0000000000000, 20091231. )
INSERT INTO DB2.T3IDRIF ( 00000000000104. , 'N' , 0000000000000, 20101231. )


And I was "worried" for the ' . ' at the end of decimal fields.
After I run this query, and I saw that DB2 accept this decimal format.
I don't know yet if this "INSERT INTO" file works on others DB...

So, just run an unload step, using the query you posted.

Thanks a lot.

Massimo