Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: Generate Insert Scripts in DB2

    Hi,

    Is there anyone who can tell me how to generate insert scripts in DB2 for all my tables?

    Any help would be appreciated.

    Regards.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    1. Make a TXT file and name it like file_name.sql with SQL statements seperated with ; character.

    Sample:
    Insert into tab1 values (1,2,3);
    Insert into tab2 values (1,2,3);
    etc.

    2. In DB2 Command Windows execute script by typing:
    db2 -tf path_and_file_name.sql

    Sample:
    db2 -tf c:\temp\file_name.sql -> On Windows
    db2 -tf /home/db2user/file_name.sql -> On Unix/Linux

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    Originally posted by grofaty
    Hi,

    1. Make a TXT file and name it like file_name.sql with SQL statements seperated with ; character.

    Sample:
    Insert into tab1 values (1,2,3);
    Insert into tab2 values (1,2,3);
    etc.

    2. In DB2 Command Windows execute script by typing:
    db2 -tf path_and_file_name.sql

    Sample:
    db2 -tf c:\temp\file_name.sql -> On Windows
    db2 -tf /home/db2user/file_name.sql -> On Unix/Linux

    Hope this helps,
    Grofaty
    Thanks for replying. What I actually need is how to generate the insert statements in the first place. I know I can do it manually like, select 'insert into table_1 (col_1, col_2 ...) values (value_1, value_2, ...)' from table_1. But I have a lot of tables, I can't possibly manually write the insert script for all of them one by one. Hence, I want to do it using some utility or tool or command easily.

    I know DB2 Commander 2000 Pro can generate insert scripts easily but there is problem with datetime field, because datetime field in DB2 has format of "2003-07-02-20.06.35.000000" but in DB Commander 2000 Pro is shown as "07/02/2003 8:06:35 PM". After generating the insert scripts from DB Commander 2000 Pro, the timestamp field will cause error when running the insert scripts.

  4. #4
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi all,
    I also tried many ways and tools including toad,but couldn't comeup with a way.

    Does anyone know about this then pls post it because it would be greate help.

    thanks
    micky

  5. #5
    Join Date
    Sep 2002
    Posts
    456

    Re: Generate Insert Scripts in DB2

    Pardon my ignorance but I don't understand the question clearly. Insert statements contain user provided value unless all the values are same then you can use java or c++ or even some scripting language to create inserts. Otherwise I don't see there is any tool which will do that for you. Other thing is that all the table might have different structure so it's hard to create something generic unless used through some programming language.

    dollar

    Originally posted by ncs_vrl
    Hi,

    Is there anyone who can tell me how to generate insert scripts in DB2 for all my tables?

    Any help would be appreciated.

    Regards.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    how about IMPORT?

    One way to accomplish this would be to create a text file with comma-delimited values (eg. using MS Excel or any text editor). You can also write a small application using whatever language you're comfortable with. Contents of that file can then be IMPORTed or LOADed into your table.

    Nick

  7. #7
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    How about creating the entire database in Oracle using JDBC Driver and then using TOAD to create the script.

    Do this only if u r very urgent and really need the script.

    mickykt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •