if exists(select * from sysobjects where id=object_id('table1')) drop table table1 goif exists(select * from sysobjects where id=object_id('table2')) drop table table2 goif exists(select * from sysobjects where id=object_id('ta')) drop table ta goset nocount on go create table table1(id int primary key,col11 int,col12 int) create table table2(id int primary key,col21 int,col22 int,col23 int) create table ta ( id int identity, table_name varchar(20), colname varchar(20), keyword int, value int )insert table1 select 1,2,3 insert table2 select 2,4,1,5 insert ta(table_name,colname,keyword,value) select 'table1','col11',1,null union all select 'table1','col12',1,null union all select 'table2','col21',2,null union all select 'table2','col22',2,null union all select 'table2','col23',2,null--select * from tadeclare @ID int,@table_name varchar(20),@colname varchar(20),@keyword int declare @sql nvarchar(200) declare cur cursor for select id,table_name,colname,keyword from ta open cur fetch next from cur into @ID,@table_name,@colname,@keyword while @@fetch_status=0 begin set @sql=N' update ta set value=(select '+@colname+ N' from '+@table_name+ N' where id='+cast(@keyword as nvarchar)+ N' )'+ N' where ID='+cast(@ID as nvarchar) --print(@sql) exec(@sql) fetch next from cur into @ID,@table_name,@colname,@keyword end close cur deallocate curselect * from tadrop table table1 drop table table2 drop table ta结果:id table_name colname keyword value ----------- -------------------- -------------------- ----------- ----------- 1 table1 col11 1 2 2 table1 col12 1 3 3 table2 col21 2 4 4 table2 col22 2 1 5 table2 col23 2 5
to vivianfdlpw():发现你好喜欢用游标哦, 以前看过一张贴子,你也是用游标的.
TO: filebat(Mark) 每個人有每個的風格嗎。就拿我來說吧。游标還不會用呢我一直都是用存儲過程呢
drop table table1
goif exists(select * from sysobjects where id=object_id('table2'))
drop table table2
goif exists(select * from sysobjects where id=object_id('ta'))
drop table ta
goset nocount on
go
create table table1(id int primary key,col11 int,col12 int)
create table table2(id int primary key,col21 int,col22 int,col23 int)
create table ta
(
id int identity,
table_name varchar(20),
colname varchar(20),
keyword int,
value int
)insert table1 select 1,2,3
insert table2 select 2,4,1,5
insert ta(table_name,colname,keyword,value)
select 'table1','col11',1,null union all
select 'table1','col12',1,null union all
select 'table2','col21',2,null union all
select 'table2','col22',2,null union all
select 'table2','col23',2,null--select * from tadeclare @ID int,@table_name varchar(20),@colname varchar(20),@keyword int
declare @sql nvarchar(200)
declare cur cursor for
select id,table_name,colname,keyword from ta
open cur
fetch next from cur into @ID,@table_name,@colname,@keyword
while @@fetch_status=0
begin
set @sql=N' update ta set value=(select '+@colname+
N' from '+@table_name+
N' where id='+cast(@keyword as nvarchar)+
N' )'+
N' where ID='+cast(@ID as nvarchar)
--print(@sql)
exec(@sql)
fetch next from cur into @ID,@table_name,@colname,@keyword
end
close cur
deallocate curselect * from tadrop table table1
drop table table2
drop table ta结果:id table_name colname keyword value
----------- -------------------- -------------------- ----------- -----------
1 table1 col11 1 2
2 table1 col12 1 3
3 table2 col21 2 4
4 table2 col22 2 1
5 table2 col23 2 5
以前看过一张贴子,你也是用游标的.
每個人有每個的風格嗎。就拿我來說吧。游标還不會用呢我一直都是用存儲過程呢
不带感情色彩.btw, 游标很费资源的