SQL codecreate table tz2008_1_1(id int,name varchar(50))
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql)
参考wzy_love_sly的代码,请问,怎样在这个联合表中增加一个字段,为每张表的表名
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql)
参考wzy_love_sly的代码,请问,怎样在这个联合表中增加一个字段,为每张表的表名
----------- --------------------------------------------------
1 a
2 b
3 c(3 行受影响)后面还加什么字段
select @sql=isnull(@sql+' union all ','')+' select *,'''+name+''' [name] from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
print @sql
exec(@sql)
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select *,'''+name+''' [name] from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql) drop table tz2008_1_1
drop table tz2008_1_2
drop table tz2008_1_3
/*id name name
----------- -------------------------------------------------- ----------
1 a tz2008_1_1
2 b tz2008_1_2
3 c tz2008_1_3(3 行受影响)
*/
select @sql=isnull(@sql+' union all ','insert into #temptable ')+' select *,'''+name+''' [name] from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
print @sql
exec(@sql)
exec("select * from #temptable")