How to generate an insert script from table in DB2

Question

I have a table in DB2 which I query to obtain a number of specific records which are of interest to me.

Now, I want to generate an insertion script for this data. The export option provided doesn't work for me because I need to provide the generation script to my client.

I read here about the possibility of using a third party tool such as DBArtisan. However, I do not have the possibility of downloading a third party tool.

I use System i Navigator with DB2® for IBM®.

EDIT: Based on @WarrenT 's question, I add more information so as to help improve my question.

Assuming the SQL statement I have to query the database is

SELECT * FROM my_table WHERE colValue>='500'

One of the suggested answers was to do:

INSERT INTO target_table 
SELECT * FROM my_table WHERE colValue>='500'

Although I do agree it would be the best solution, I cannot do this because in the target environment they do not have the table "my_table". I'm no expert in the different script types, I know I need the typical script

INSERT INTO target_table (parm1, parm2,...) VALUES (val1, val2,...)

If both machines are on IBM i, is FTP a viable option? Or DDM? — WarrenT
– WarrenT, Commented Jun 5, 2013 at 0:08
No, sadly it isn't a viable option. DDM? Sorry, no idea what that is! — Soph
– Soph, Commented Jun 5, 2013 at 12:12

WarrenT · Accepted Answer · 2013-06-01 22:48:30Z

2

So you have a SQL query that selects the records you to be inserted into another table?

You probably simply want to use the INSERT command. If you are going to populate all the columns in your target table, then you can use this format:

INSERT INTO your_target_table
    SELECT your query goes here;

This works if the columns returned by your query match up to the columns in the destination table. If not, then you have to list the target columns you will be populating.

See INSERT in the SQL Reference manual for more details.

answered Jun 1, 2013 at 22:48

WarrenT

4,54021 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Soph Over a year ago

The problem is I can't use that method. Due to policies I have to hand in a script, not an SQL statement.

WarrenT Over a year ago

Define what you mean by script. If you save statements from i Navigator's Run SQL Statements session, in a PC file with a .SQL suffix, then that is often referred to as an SQL script. Another type is to store the statement(s) in a source file member, usually QSQLSRC, in a library (ie schema) on the iSeries. This can be run via the STRSQLSTM CL command. Unix-type she'll scripts are not common on this platform, but can be run under Qshell or PASE (an AIX-like environment).

Soph Over a year ago

My SQL query that selects the required records has a simple condition where a column value has to be greater than 500. However, I cannot pass on the SQL "INSERT INTO target_table SELECT my_simple_query;" because in the target environment they do not have this table which "my_simple_query" needs. I'm no expert in the different script types, I know I need the typical script "INSERT INTO target_table (parm1, parm2,...) VALUES (val1, val2,...)"

WarrenT Over a year ago

Ah, you want to transfer data from one system to another via a script. Are both platforms DB2 for i? If so, and you are lucky enough to have recent technology refreshes applied, then actually yes you might be able to use 3-part naming to INSERT directly from one system to another. About how many rows do you have to transfer?

Collectives™ on Stack Overflow

How to generate an insert script from table in DB2

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related