利用游标在Sql Server里复制父子关联表数据

qianqianling

源表为t_src_table_parent和t_src_table_child，为父子关系
目的表为t_dest_table_parent和t_dest_table_child，表结构与源表一致
目标是将源表的数据原样插入到目的表中，父子关系不变，transaction sql如下：

declare
@t_udf_id int,@t_aspaccount_id int,@field_name varchar (32),@name_en nvarchar (50),@name_cn nvarchar (50),@name_en_enterprise nvarchar (50),@name_cn_enterprise nvarchar (50),@field_type char (1),@field_length int,@field_decimal smallint,@descr nvarchar (200),@status char (1),@is_enterprise char (1),@seq_no int,@creator_id int,@modifier_id int
declare
@option_T_UDF_OPTION_id int,@option_t_udf_id int,@option_t_aspaccount_id int,@option_option_name nvarchar (100),@option_seq_no smallint,@option_aliases nvarchar,@option_is_buildin char (1)
declare @loop_counter int,@total_count int,@outer_loop_counter int,@outer_total_count int
select @outer_total_count = Count(*)  from t_src_table_parent
DECLARE src_table_parent_cursor CURSOR FOR
SELECT
t_udf_id,t_aspaccount_id,field_name,name_en,name_cn,name_en_enterprise,name_cn_enterprise,field_type,field_length,field_decimal,descr,status,is_enterprise,seq_no,creator_id,modifier_id
FROM t_src_table_parent
open src_table_parent_cursor
set @outer_loop_counter=0
WHILE @outer_loop_counter <@outer_total_count
Begin
FETCH  NEXT FROM src_table_parent_cursor
INTO
@t_udf_id,@t_aspaccount_id,@field_name,@name_en,@name_cn,@name_en_enterprise,@name_cn_enterprise,@field_type,@field_length,@field_decimal,@descr,@status,@is_enterprise,@seq_no,@creator_id,@modifier_id
insert into t_dest_table_parent(
t_udf_id,t_aspaccount_id,field_name,name_en,name_cn,name_en_enterprise,name_cn_enterprise,field_type,field_length,field_decimal,descr,status,is_enterprise,seq_no,creator_id,modifier_id
) values(
@t_udf_id,@t_aspaccount_id,@field_name,@name_en,@name_cn,@name_en_enterprise,@name_cn_enterprise,@field_type,@field_length,@field_decimal,@descr,@status,@is_enterprise,@seq_no,@creator_id,@modifier_id
)
------处理子表
select @total_count = Count(*)  from t_src_table_child where t_udf_id=@t_udf_id
DECLARE t_udf_option_test_cursor CURSOR FOR
SELECT
T_UDF_OPTION_id,t_udf_id,t_aspaccount_id,option_name,seq_no,aliases,is_buildin
FROM t_src_table_child
where t_udf_id=@t_udf_id
open t_udf_option_test_cursor
set @loop_counter=0
WHILE @loop_counter <@total_count
BEGIN
FETCH  NEXT FROM t_udf_option_test_cursor
INTO
@option_T_UDF_OPTION_id,@option_t_udf_id,@option_t_aspaccount_id,@option_option_name,@option_seq_no,@option_aliases,@option_is_buildin
insert into t_dest_table_child(
T_UDF_OPTION_id,t_udf_id,t_aspaccount_id,option_name,seq_no,aliases,is_buildin)
values(
@option_T_UDF_OPTION_id,@option_t_udf_id,@option_t_aspaccount_id,@option_option_name,@option_seq_no,@option_aliases,@option_is_buildin
)
set @loop_counter = @loop_counter + 1
END
close t_udf_option_test_cursor
DEALLOCATE t_udf_option_test_cursor
set @outer_loop_counter = @outer_loop_counter + 1
end
close src_table_parent_cursor
DEALLOCATE src_table_parent_cursor