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

源表为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