34,576
社区成员
发帖
与我相关
我的任务
分享
--测试数据,待合并表;
create table a_s1(
id int
);
go
create table a_s2(
id int
);
go
create table a_s3(
id int
);
go
--测试数据;
insert a_s1
select 1 union all
select 2;
go
insert a_s2
select 2 union all
select 3;
go
insert a_s3
select 3 union all
select 4 union all
select 5
go
select * from a_s1;
select * from a_s2;
select * from a_s3;
--临时表,合并数据至此表;
create table #tt
(
id int identity(1,1),
value int
)
go
select * from #tt;
--开始创建存储过程;
if OBJECT_ID('pro_test') is not null
begin
drop proc pro_test;
end;
go
--创建存储过程;
CREATE PROC pro_test
AS
BEGIN
--声明变量,存放待执行动态sql语句;
DECLARE @sql varchar(max);
--声明变量,用于存放表名;
DECLARE @tableName varchar(max);
--声明游标,用于读取表名;
DECLARE myCursor CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U' and category=0
and CHARINDEX('a_',name)=1;
--打开游标
OPEN myCursor;
--开始读取;
FETCH NEXT FROM myCursor
INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @tableName;
--动态创建sql语句;
set @sql = 'MERGE INTO #tt AS t ' +
' using (select id from ' + @tableName + ') as s ' +
' on t.id = s.id ' +
' when not matched then ' +
' insert (value) values (id);';
--print @sql;
--执行;
exec(@sql);
FETCH NEXT FROM myCursor
INTO @tableName;
END;
--关闭游标;
CLOSE myCursor;
DEALLOCATE myCursor;
END;
pro_test;
--查看数据
select * from #tt;
--给你一个例子:
create table a_s1(
id int
)
go
create table a_s2(
id int
)
go
create table a_s3(
id int
)
insert a_s1
select 1
insert a_s2
select 2
insert a_s3
select 2 union all
select 3
go
create table #tt(
id int identity(1,1),
value int
)
go
if OBJECT_ID('pro_test') is not null
drop proc pro_test
go
create proc pro_test
as
declare @str varchar(max)
set @str=''
select @str=@str+'select * from '+name+CHAR(10)+' union all '+CHAR(10)
from sysobjects
where xtype='U' and category=0
and CHARINDEX('a_',name)=1
select @str=LEFT(@str,LEN(@str)-12)
exec('insert #tt(value) select * from ('+@str+')t')
delete a from #tt a
where exists(select 1 from #tt b where a.value=b.value and a.id<b.id)
go
exec pro_test
select * from #tt
/*
id value
1 1
3 2
4 3
*/
--一个简单的例子
DECLARE @t DATETIME,@s VARCHAR(max),@i INT,@d INT
SET @t=GETDATE()--表示取当月的所有表数据
set @d=DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,0,@t),0),DATEADD(mm,DATEDIFF(mm,0,@t)+1,0))
SET @i=0
WHILE @i<@d
BEGIN
SET @s=ISNULL(@s+'union all'+CHAR(13),'')+'select * from a_'+CONVERT(VARCHAR(8),DATEADD(dd,@i,DATEADD(mm,DATEDIFF(mm,0,@t),0)),112)+CHAR(13)
SET @i=@i+1
END
PRINT @s
/*
select * from a_20120701
union all
select * from a_20120702
union all
select * from a_20120703
union all
select * from a_20120704
union all
select * from a_20120705
union all
select * from a_20120706
union all
select * from a_20120707
union all
select * from a_20120708
union all
select * from a_20120709
union all
select * from a_20120710
union all
select * from a_20120711
union all
select * from a_20120712
union all
select * from a_20120713
union all
select * from a_20120714
union all
select * from a_20120715
union all
select * from a_20120716
union all
select * from a_20120717
union all
select * from a_20120718
union all
select * from a_20120719
union all
select * from a_20120720
union all
select * from a_20120721
union all
select * from a_20120722
union all
select * from a_20120723
union all
select * from a_20120724
union all
select * from a_20120725
union all
select * from a_20120726
union all
select * from a_20120727
union all
select * from a_20120728
union all
select * from a_20120729
union all
select * from a_20120730
union all
select * from a_20120731
*/
--给你一个例子:
create table a_s1(
id int
)
go
create table a_s2(
id int
)
go
create table a_s3(
id int
)
go
declare @str varchar(max)
set @str=''
select @str=@str+'select * from '+name+CHAR(10)+' union all '+CHAR(10)
from sysobjects
where xtype='U' and category=0
and CHARINDEX('a_',name)=1
select @str=LEFT(@str,LEN(@str)-12)
print @str
/*
select * from a_s1
union all
select * from a_s2
union all
select * from a_s3
*/