库房管理系统中上月数据成批追加到本月的问题

[ljyyhlp]

数据表的结构如下:

month int
a1 number
a2 number
a3 number
a4 number
如何将上月a3,a4 列中的数据成批追加到本 月a1,a2列中.

[吹雪风]

假设table名称:tbl_store,上月时间:200703,本月即为200704,那么SQL 语句如下:

insert into tbl_store (select 200704,a3,a4,0,0 from tbl_store where month = 200703);

[ljyyhlp]

如果用游标该怎么使用

[吹雪风]

假设如上不变,用游标方法如下:(a3,a4假设为三位小数,dec{3})

dec{3} ld_a3,ld_a4

declare cursor cur_sel for
select a3,a4 from tbl_store where month = 200703;

open cur_sel;

fetch cur_sel into :ld_a3,:ld_a4;

do while sqlca.sqlcode = 0
insert into tbl_store values(200704,:ld_a3,:ld_a4,0,0);
fetch cur_sel into :ld_a3,:ld_a4;
loop

close cur_sel;

commit;

//此处用游标效率不如直接用insert语句高...另外代码均未测试,请测试一下~

[ljyyhlp]

insert into tbl_store (select 200704,a3,a4,0,0 from tbl_store where month = 200703);
这句我试了,出错?

[吹雪风]

怎么出错,提示是什么?

下面是MS SQL SERVER的帮助:
用 SELECT 子查询为一行或多行指定数据值。
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView