表A:
一类 二类 3类 4类 中文
==============================
1 1
1 12 12
2 2
2 21 21
2 21 23 23表B:
ID name name_cn sort_level sort_code
1 1 1 0
2 12 2 1
3 2 2 0
4 21 2 3
5 23 3 4
我自己想写一个存储过程,但是中间的游标那里写不出来,或者大家有更好的方法:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertData2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertData2]
GOCREATE PROCEDURE sp_InsertData2
@sourceTB varchar(50),----源表
@objectTB varchar(50),----目标表
@sel_field nvarchar(255),---查询字段
@sel_qualification nvarchar(255),---查询条件
@SortNum int ----类别数:1,2,3,4
as
declare @sel_sqlstring nvarchar(255)-----查询语句
declare @into_sqlstring nvarchar(255)---插入语句if @SortNum=1
begin
set @sel_sqlstring='select 一类,中文 from '+@sourceTB+' where 二类 is NULL and 三类 is NULL and 四类 is NULL'
end
else if @SortNum=2
begin
set @sel_sqlstring='select 二类,中文 from '+@sourceTB+' where 二类 is not null and 三类 is NULL and 四类 is NULL'
end
else if @SortNum=3
begin
set @sel_sqlstring='select 三类,中文 from '+@sourceTB+' where 三类 is not null and 四类 is NULL'
end
else
begin
set @sel_sqlstring='select 四类,中文 from '+@sourceTB+' where 四类 is not NULL'
end
create table #t(a varchar(255),b varchar(255))
insert into #t exec (@sel_sqlstring)declare @SelValue varchar
declare Num_Cursor CURSOR FOR --定义游标
exec (@sel_sqlstring)
open Num_Cursor --打开游标
fetch next from Num_Cursor into @SelValue
while @@fetch_status = 0
begin
/*
set @into_sqlstring='insert into new_sort(sort_name,sort_name_en,sort_level,sort_code) select'+@sel_field+',中文对照,'+@SortNum+',0 from #t where '+@sel_qualification +'and '
*/ fetch next from #W into @SelValue --循环
end
close Num_Cursor --关闭游标
deallocate Num_Cursor --销毁游标
/*===============================
=============================*/
GO
一类 二类 3类 4类 中文
==============================
1 1
1 12 12
2 2
2 21 21
2 21 23 23表B:
ID name name_cn sort_level sort_code
1 1 1 0
2 12 2 1
3 2 2 0
4 21 2 3
5 23 3 4
我自己想写一个存储过程,但是中间的游标那里写不出来,或者大家有更好的方法:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertData2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertData2]
GOCREATE PROCEDURE sp_InsertData2
@sourceTB varchar(50),----源表
@objectTB varchar(50),----目标表
@sel_field nvarchar(255),---查询字段
@sel_qualification nvarchar(255),---查询条件
@SortNum int ----类别数:1,2,3,4
as
declare @sel_sqlstring nvarchar(255)-----查询语句
declare @into_sqlstring nvarchar(255)---插入语句if @SortNum=1
begin
set @sel_sqlstring='select 一类,中文 from '+@sourceTB+' where 二类 is NULL and 三类 is NULL and 四类 is NULL'
end
else if @SortNum=2
begin
set @sel_sqlstring='select 二类,中文 from '+@sourceTB+' where 二类 is not null and 三类 is NULL and 四类 is NULL'
end
else if @SortNum=3
begin
set @sel_sqlstring='select 三类,中文 from '+@sourceTB+' where 三类 is not null and 四类 is NULL'
end
else
begin
set @sel_sqlstring='select 四类,中文 from '+@sourceTB+' where 四类 is not NULL'
end
create table #t(a varchar(255),b varchar(255))
insert into #t exec (@sel_sqlstring)declare @SelValue varchar
declare Num_Cursor CURSOR FOR --定义游标
exec (@sel_sqlstring)
open Num_Cursor --打开游标
fetch next from Num_Cursor into @SelValue
while @@fetch_status = 0
begin
/*
set @into_sqlstring='insert into new_sort(sort_name,sort_name_en,sort_level,sort_code) select'+@sel_field+',中文对照,'+@SortNum+',0 from #t where '+@sel_qualification +'and '
*/ fetch next from #W into @SelValue --循环
end
close Num_Cursor --关闭游标
deallocate Num_Cursor --销毁游标
/*===============================
=============================*/
GO
========数据======================
1
1 12
2
2 21
2 21 23
2 21 23 24 导入下面的表ID name name_cn sort_level sort_code
================数据=========================
1 1 1 0
2 12 2 1
3 2 2 0
4 21 2 3
5 23 3 4
6 24 4 5
========数据======================
1
1 12
2
2 21
2 21 23
2 21 23 24 导入下面的表,变成下面这个的排列ID name name_cn sort_level sort_code
================数据=========================
1 1 1 0
2 12 2 1
3 2 2 0
4 21 2 3
5 23 3 4
6 24 4 5
可以说是,两个不同结构的表,把其中一个表的数据重新整理再导进另外一个表中