使用游标将一张sqlserver表拆成两张父子表

本文主要是介绍使用游标将一张sqlserver表拆成两张父子表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

需求：要将一张sqlserver表拆成两张父子表，

记录一下代码

--目标：一张表拆成两张父[dbo.ra_device]子[dbo.ra_device_detail]表--查看表结构
sp_help  ra_device_detail --为父子表各添加字段
alter table dbo.ra_device add row_id nchar(200);
alter table dbo.ra_device add update_time datetime;
alter table dbo.ra_device add is_del tinyint default 0;
alter table dbo.ra_device add s_id nchar(200);alter table dbo.ra_device_detail add row_id nchar(200);
alter table dbo.ra_device_detail add update_time datetime;
alter table dbo.ra_device_detail add is_del tinyint default 0;
alter table dbo.ra_device_detail add s_id nchar(200);select *  from dbo.ra_demo
select * from dbo.ra_device_detail
select * from dbo.ra_device
truncate table dbo.ra_device--下为父子表游标SQLDECLARE @ID nchar(200) 
, @BU nchar(200)  
, @Productname NVARCHAR(1000) 
, @Producttype NVARCHAR(1000)  
, @ref_num nchar(200) 
, @producttype1 nchar(200)  
, @producttype2 nchar(200) 
, @reviewer NVARCHAR(1000)
, @review_dept NVARCHAR(1000) 
, @re_date date
, @v_id nchar(200)
, @v_time datetime
--shang父子表变量分割线--
, @re_file NVARCHAR(1000) 
, @re_content NVARCHAR(1000)
, @re_type NVARCHAR(1000) 
, @comment NVARCHAR(1000);DECLARE mycursor CURSOR  
FOR  SELECT ID,BU,Productname,Producttype,ref_num,producttype1,producttype2,reviewer,review_dept,re_date,re_file,re_content,re_type,comment FROM ra_demo 
--为变量赋值set @v_id = 0;set @v_time = GETDATE();
OPEN mycursor  --打开游标       
--从游标里取出数赋值到我们刚才声明的变量中(移动游标指向到第一条数据，提取第一条数据存放在变量中)    
FETCH NEXT FROM mycursor INTO @ID, @BU,@Productname,@Producttype,@ref_num,@producttype1,@producttype2,@reviewer,@review_dept,@re_date,@re_file,@re_content,@re_type,@comment;
--判断游标的状态    
-- 0 fetch语句成功        
---1 fetch语句失败或此行不在结果集中        
---2 被提取的行不存在    
WHILE @@fetch_status = 0   --如果上一次操作成功则继续循环BEGIN        --显示出我们每次用游标取出的值     --print (@Id+'--------'+@UserName+'--------'+@Password+'----'+@NickName)--条件判断set @v_id = @v_id+1;--父表插入INSERT INTO ra_device(ID,BU,Productname,Producttype,ref_num,producttype1,producttype2,reviewer,review_dept,re_date,row_id,p_id,update_time)VALUES(@ID, @BU,@Productname,@Producttype,@ref_num,@producttype1,@producttype2,@reviewer,@review_dept,@re_date,@v_id,@v_id,@v_time)--子表插入INSERT INTO ra_device_detail(ID,ref_num,re_file,re_content,re_type,comment,row_id,s_id,update_time)VALUES(@ID,@ref_num,@re_file,cast(@re_content as ntext),@re_type,cast(@comment as ntext),@v_id,@v_id,@v_time);--用游标去取下一条记录(继续取下一行数据)FETCH NEXT FROM mycursor INTO @ID, @BU,@Productname,@Producttype,@ref_num,@producttype1,@producttype2,@reviewer,@review_dept,@re_date,@re_file,@re_content,@re_type,@comment;END--打印总插入行数print(@v_id)CLOSE mycursor; --关闭游标     DEALLOCATE mycursor; --撤销游标(释放资源 )