select Name,Age into #t from People where 1<>1declare @Sql varchar(800) declare @str varchar(8000) set @str='insert into #t(Name,Age) ' set @Sql = ' SELECT Name, Age FROM People WHERE Age=20' set @str=@str+@Sql exec(@str)
create table #t (Sql Varchar(8000)) DECLARE @Sql varchar(8000) set @Sql ='SELECT Name, Age FROM People WHERE Age=20' insert into #t select @Sql select * from #t drop table #t(所影响的行数为 1 行)Sql ------------------------------------------------------------------------------------------ SELECT Name, Age FROM People WHERE Age=20(所影响的行数为 1 行)
#t不确定结构, @Sql 是动态拼出来的。
真晕,实在不好意思,搞错了!insert into #t exec(@Sql)
create table #t (Sql Varchar(8000)) DECLARE @Sql varchar(8000) set @Sql ='SELECT Name, Age FROM People WHERE Age=20' insert into #t select @Sql select * from #t drop table #t服务器: 消息 213,级别 16,状态 7,行 1 [Microsoft][ODBC SQL Server Driver][SQL Server]插入错误: 列名或所提供值的数目与表定义不匹配。你Create Table 已经把结构定了.
这样也可以! select * into #t from (SELECT Name, Age FROM People WHERE Age=20) a select * from #t
这样也没问题,但表t前面不能加#,否则看不到结果 DECLARE @Sql varchar(8000) Set @Sql = SELECT Name, Age FROM People WHERE Age=20' exec('select * into t from ('+@Sql+' ) a') select * from t
chiwei(水手) : t是个什么表啊? session临时? 全局临时? 还有后面的a是什么.
如果我还要在t中加入 @Sql = SELECT Name, Age FROM People WHERE Age=30' 执行的内容如何做。
t也是个新建的表,但必须手动删除(drop table t) a 只是表(SELECT Name, Age FROM People WHERE Age=20')的别名
如果我还要在t中加入 @Sql = SELECT Name, Age FROM People WHERE Age=30' 执行的内容如何做。 ————————————————————————————————————————————可以改改上面的语句来实现 DECLARE @Sql varchar(8000) Set @Sql = 'SELECT Name, Age FROM People WHERE Age=30' exec('select * into t from ('+@Sql+' ) a') select * from t
或者直接改你定义的条件DECLARE @Sql varchar(8000) Set @Sql = 'SELECT Name, Age FROM People WHERE Age=20 or Age=30' exec('select * into t from ('+@Sql+' ) a') select * from t
@Sql = @SqlFront + 'Age=' + @CurAge --如: @Sql = SELECT Name, Age FROM People WHERE Age=30' , 如果Step为5,下次条件为Age=35 执行@Sql 结果累如临时表 } 返回临时表
--测试数据 Create table People (name varchar(50),age int) insert into People select 'A',20 union all select 'B',25 union all select 'C',30 union all select 'D',35 union all select 'E',40go --申明变量 declare @Prm_SqlFront varchar(500) --Sql的前面部分 declare @prm_AgeStep int --Age间隔步长 declare @Prm_AgeStart int declare @Prm_AgeEnd int declare @CurAge int set @Prm_SqlFront='SELECT Name, Age FROM People WHERE ' set @Prm_AgeStep=5 set @Prm_AgeStart=25 set @Prm_AgeEnd=80While (@Prm_AgeStart<@Prm_AgeEnd) BEGIN set @CurAge = @Prm_AgeStart + @Prm_AgeStep set @Prm_AgeStart = @CurAge exec('select * into t from ('+@Prm_SqlFront+'age= '+ @CurAge+' ) a') select * from t drop table t END--删除测试表 drop table people
declare @str varchar(8000)
set @str='insert into #t(Name,Age) '
set @Sql = ' SELECT Name, Age FROM People WHERE Age=20'
set @str=@str+@Sql
exec(@str)
DECLARE @Sql varchar(8000)
set @Sql ='SELECT Name, Age FROM People WHERE Age=20'
insert into #t select @Sql
select * from #t
drop table #t(所影响的行数为 1 行)Sql
------------------------------------------------------------------------------------------
SELECT Name, Age FROM People WHERE Age=20(所影响的行数为 1 行)
DECLARE @Sql varchar(8000)
set @Sql ='SELECT Name, Age FROM People WHERE Age=20'
insert into #t select @Sql
select * from #t
drop table #t服务器: 消息 213,级别 16,状态 7,行 1
[Microsoft][ODBC SQL Server Driver][SQL Server]插入错误: 列名或所提供值的数目与表定义不匹配。你Create Table 已经把结构定了.
select * into #t from (SELECT Name, Age FROM People WHERE Age=20) a
select * from #t
DECLARE @Sql varchar(8000)
Set @Sql = SELECT Name, Age FROM People WHERE Age=20'
exec('select * into t from ('+@Sql+' ) a')
select * from t
t是个什么表啊? session临时? 全局临时? 还有后面的a是什么.
a 只是表(SELECT Name, Age FROM People WHERE Age=20')的别名
如果我还要在t中加入 @Sql = SELECT Name, Age FROM People WHERE Age=30' 执行的内容如何做。
————————————————————————————————————————————可以改改上面的语句来实现
DECLARE @Sql varchar(8000)
Set @Sql = 'SELECT Name, Age FROM People WHERE Age=30'
exec('select * into t from ('+@Sql+' ) a')
select * from t
Set @Sql = 'SELECT Name, Age FROM People WHERE Age=20 or Age=30'
exec('select * into t from ('+@Sql+' ) a')
select * from t
反复执行条件不同的@Sql, 当然@Sql中的SELECT ...段在每次调用过程时是一定的是由外部来决定
把每次执行的结果累在临时表里.
在返回, 可以做到吗??
入口参数:
@prm_SqlFront, --Sql的前面部分
@prm_AgeStep, --Age间隔步长
@prm_AgeStart,
@prm_AgeEnd需要的效果:
while(到AgeEnd?)
{
@CurAge = @prm_AgeStart + @prm_AgeStep
@prm_AgeStart = @CurAge
@Sql = @SqlFront + 'Age=' + @CurAge
--如: @Sql = SELECT Name, Age FROM People WHERE Age=30' , 如果Step为5,下次条件为Age=35
执行@Sql 结果累如临时表
}
返回临时表
Create table People (name varchar(50),age int)
insert into People select 'A',20
union all select 'B',25
union all select 'C',30
union all select 'D',35
union all select 'E',40go
--申明变量
declare @Prm_SqlFront varchar(500) --Sql的前面部分
declare @prm_AgeStep int --Age间隔步长
declare @Prm_AgeStart int
declare @Prm_AgeEnd int
declare @CurAge int
set @Prm_SqlFront='SELECT Name, Age FROM People WHERE '
set @Prm_AgeStep=5
set @Prm_AgeStart=25
set @Prm_AgeEnd=80While (@Prm_AgeStart<@Prm_AgeEnd)
BEGIN
set @CurAge = @Prm_AgeStart + @Prm_AgeStep
set @Prm_AgeStart = @CurAge
exec('select * into t from ('+@Prm_SqlFront+'age= '+ @CurAge+' ) a')
select * from t
drop table t
END--删除测试表
drop table people