create table #tb_Name(Id int,tbName varchar(20))
insert into #tb_Name values(1,'chat')
insert into #tb_Name values(2,'sms')
insert into #tb_Name values(3,'mms')
select * from #tb_Name先帮大家 建 个表
insert into #tb_Name values(1,'chat')
insert into #tb_Name values(2,'sms')
insert into #tb_Name values(3,'mms')
select * from #tb_Name先帮大家 建 个表
declare @id int
declare @name varchar(20)select @sql = ''
declare cc cursor for select * from #tb_Name
open cc
while 1=1
begin
fetch next from cc into @id,@name
if @@fetch_status =-1 break
if @@fetch_status =-2 continue if @sql = ''
select @sql = @sql + 'select *,'''+@name +''' as sys from '+@name
else
select @sql = @sql + ' union all select *,'''+@name +''' as sys from '+@name
end
close cc
select @sqlexec @sql
把你的chat sms mms表中所有的数据累加成一个表,用一个字段来区分每条纪录来源于那个表,即我代码中的sys字段。
--如果是累加..
一條靜態SQL是完成不了的,可以使用動態SQL來實現.(從Tb_Name表中運態獲取表名,來生成查詢用的SQL語句..,生成的查詢用SQL語句類似于:
SELECT * FROM Chat
Union
SELECT * FROM SMS
Union
SELECT * FROM MMS
與手工的SQL不同的是,其中的表名項是從Tb_Name中動態取出的...表多幾張少幾表都行,語句不變
下面給出一具體的代碼示例:
use pubs
GO--生成測試用表
Declare @Tb Table(ID int, tbName varchar(10))
INSERT INTO @Tb SELECT 1,'chat'
UNION ALL SELECT 2,'sms'
UNION ALL SELECT 3,'mms'SELECT * FROM @TbDeclare @Sql Varchar(8000)
Set @Sql=''
SELECT @Sql= @sql+ ' SELECT * FROM '+tbName+' UNION ALL' FROM @Tb
Set @sql=substring(@sql,1,len(@sql)-9)
--測試動態生成的SQL
SELECT @sql as 動態得到的執行語句
--如果正确則執行下面的語句,注:需要放在EXEC()中執行
--exec(@sql)
---------------------------------------------
SELECT * FROM chat UNION ALL SELECT * FROM sms UNION ALL SELECT * FROM mms (所影响的行数为 1 行)
游標的效率是很低的...