在DB2中插入数千行 [英] insert thousands of rows in DB2
问题描述
我在DB2中的知识很少,我只是找不到具体的简单的回答如何做到这一点。有人可以给我一个如何实现这个的例子?
DB2版本:9.7 / OS:Windows 2k8
例如我有下表:
T_RES_TABLE
Col1 | Col2 | Col3 | Col4 |
----------------------
1 | 1 | 1 | 1 |
我需要实现的是:
T_RES_TABLE
Col1 | Col2 | Col3 | Col4 |
----------------------
1 | 1 | 1 | 1 | - 原始
----------------------
2 | 1 | 2 | 1 |
----------------------
。
。
。
----------------------
18000 | 1 | 18000 | 1 |
所以Col1和Col3需要增加,其余的必须保持原样。希望很清楚。
您可以使用递归查询来生成新的列值:
$ db2create table t_res_table(col1 int,col2 int,col3 int,col4 int)
DB20000I SQL命令已成功完成。
$ db2insert into t_res_table values(1,1,1,1)
DB20000I SQL命令成功完成。
$ db2select * from t_res_table
COL1 COL2 COL3 COL4
----------- ------- ---- ----------- -----------
1 1 1 1
1选择的记录。
$ db2插入到t_res_table \
>与t(col1,col2,col3,col4,lvl)为(\
>选择col1,col2, col3,col4,1来自t_res_table其中col1 = 1 \
> union all \
>从t选择col1 + 1,col2,col3 + 1,col4,lvl + 1,其中lvl< ; 18)\
>从t选择col1,col2,col3,col4 col1> 1
DB20000I SQL命令成功完成。
$ db2select * 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个选择的记录。
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.
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.
这篇关于在DB2中插入数千行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!