insert thousands of rows in DB2

Question

I need to multiply a certain row in a DB2 table 18000 times. Some of the column values will remain as the original and some need to be incremented by 1.

I have very little knowledge in DB2 and I just can't find a concrete simple answer on how to do this. Can someone please give me an example on how to achieve this?

DB2 version: 9.7 / OS: Windows 2k8

For example I have the following table:

T_RES_TABLE
Col1 |Col2|Col3 |Col4|
----------------------
1    |   1|    1|   1|

What I need to achieve is:

T_RES_TABLE
Col1 |Col2|Col3 |Col4|
----------------------
1    |   1|    1|   1| - original
----------------------
2    |   1|    2|   1|
----------------------
.
.
.
----------------------
18000|   1|18000|   1|

So Col1 and Col3 need to increment and the rest must stay as is. Hope it's clear enough.

Answer 1

This will do your job. Just change it as your needs, change table name and column names and hit run. I dont know what you want to achieve but, it sounds you just need an Dummy Data in a table. So, i just make it for dummy purpose.

Modify it for your another requirements. The example below contains everything what you need, but you may have to modify according to that.

DECLARE @I INT=0
SET @I=1
DECLARE @LASTF2 INT
DECLARE @LASTF4 INT

SELECT @LASTF2=ISNULL(MAX(F2),1) FROM TEST --JUST CHANGE TEST TO YOUR TABLENAME, AND     F1,F2,F3,F4 
SELECT @LASTF4=ISNULL(MAX(F4),1) FROM TEST -- AS YOUR FIELD NAMES

WHILE @I<10000
BEGIN
    INSERT INTO TEST (F1,F2,F3,F4)
    VALUES (@I,@LASTF2,@I,@LASTF4)

    SET @I=@I+1
END
GO
SELECT * FROM TEST

Answer 2

You can use a recursive query to generate new column values:

    $ db2 "create table t_res_table (col1 int, col2 int, col3 int, col4 int)"
    DB20000I  The SQL command completed successfully.

    $ db2 "insert into t_res_table values (1,1,1,1)"
    DB20000I  The SQL command completed successfully.

    $ db2 "select * from t_res_table"

    COL1        COL2        COL3        COL4       
    ----------- ----------- ----------- -----------
                1           1           1           1

      1 record(s) selected.

    $ db2 "insert into t_res_table \
    >with t (col1, col2, col3, col4, lvl) as ( \
    >select col1, col2, col3, col4, 1  from t_res_table where col1 =1 \
    >union all \
    >select col1+1, col2, col3+1, col4, lvl+1 from t where lvl <18) \
    >select col1, col2, col3, col4 from t where col1 > 1"
    DB20000I  The SQL command completed successfully.

    $ db2 "select * from t_res_table"

    COL1        COL2        COL3        COL4       
    ----------- ----------- ----------- -----------
                1           1           1           1
                2           1           2           1
                3           1           3           1
                4           1           4           1
                5           1           5           1
                6           1           6           1
                7           1           7           1
                8           1           8           1
                9           1           9           1
               10           1          10           1
               11           1          11           1
               12           1          12           1
               13           1          13           1
               14           1          14           1
               15           1          15           1
               16           1          16           1
               17           1          17           1
               18           1          18           1

      18 record(s) selected.