22,209
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..##ScraperReportTmp') IS NOT NULL
BEGIN
DROP TABLE ##ScraperReportTmp
END
DECLARE @SQL NVARCHAR(MAX)
DECLARE @_CutSQL NVARCHAR(MAX)
SET @_CutSQL='SELECT * FROM dbo.TFReport'
SET @SQL=
'SELECT
*
INTO ##ScraperReportTmp FROM (
'+ @_CutSQL +'
) A'
EXEC(@SQL)
SELECT * FROM ##ScraperReportTmp
create table t1(id int,col varchar(10))
insert into t1 select 1,'aaa'
insert into t1 select 2,'bbb'
create table t2(abc int,def varchar(10))
insert into t2 select 243,'fawed'
insert into t2 select 12344,'bbasdwaeb'
create table t3(faew int,faead varchar(10))
insert into t3 select 64523,'vasdf'
insert into t3 select 23,'fq34fd'
go
create procedure unionallrecord(
@sql1 nvarchar(max),
@sql2 nvarchar(max),
@sql3 nvarchar(max)
--如还有,继续
)as
begin
exec(@sql1)
exec(@sql2)
exec(@sql3)
--如还有,继续
end
go
create table #t(id int,col varchar(10))
insert into #t
exec unionallrecord 'select * from t1','select * from t2','select * from t3'
select * from #t
/*
id col
----------- ----------
1 aaa
2 bbb
243 fawed
12344 bbasdwaeb
64523 vasdf
23 fq34fd
(6 行受影响)
*/
go
drop table t1,t2,t3,#t
drop procedure unionallrecord
SET @_CutSQL='SELECT * FROM dbo.TFReport union all select * from dbo.othertable1 union all select * from dbo.othertable2 union all...'
SET @SQL=
'SELECT
*
INTO ##ScraperReportTmp FROM (
'+ @_CutSQL +'
) A'
EXEC(@SQL)
SELECT * FROM ##ScraperReportTmp
INTO ##ScraperReportTmp--改為
Create table #ScraperReportTmp
insert #ScraperReportTmp EXEC(@SQL)