SELECT a.* INTO # FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=xxx', 'EXEC 库名.dbo.过程名 参数') AS a
declare @S varchar(8000) create table #B(I int) set @S = 'select Year(getdate())' insert into #B(I) exec(@S) select * from #B drop table #B----------------(所影响的行数为 1 行) (所影响的行数为 1 行) I 2007
--建立测试环境 set nocount on create table test(model varchar(20),date int ,qty int) insert into test select 'a','8','10' insert into test select 'a','10','50' insert into test select 'b','8','100' insert into test select 'b','9','200' insert into test select 'b','10','100' insert into test select 'c','10','200' insert into test select 'd','10','300' insert into test select 'e','11','250' insert into test select 'e','12','100' insert into test select 'f','12','150' go --测试declare @sql varchar(8000) declare @tablename varchar(100) set @tablename='t_'+replace(newid(),'-','') set @sql='select model,' select @sql=@sql+'sum(case when date='''+cast(date as varchar(10))+''' then qty else 0 end)['+cast(date as varchar(10))+'],' from (select distinct top 100 percent date from test order by date)aset @sql =left(@sql,len(@sql)-1)+' into '+@tablename+' from test group by model'exec(@sql)exec ('select * from '+@tablename) --删除测试环境 drop table test exec ('drop table '+@tablename) set nocount off/* model 8 9 10 11 12 -------------------- ----------- ----------- ----------- ----------- ----------- a 10 0 50 0 0 b 100 200 100 0 0 c 0 0 200 0 0 d 0 0 300 0 0 e 0 0 0 250 100 f 0 0 0 0 150 */
declare @s varchar(8000) set @s = 'select ..... into ## from .......' exec(@s) select * from ## drop table ##
写一个存储过程 生成一个表:declare @s nvarchar(4000) set @s='' select @s=........... set @s='select '+@s+' into Tmp from T' select * from syscolumns where ID=object_ID('T')--得到表的结构...处理结果集 -- if not object_id('Tmp') is null--存在删除 exec('drop table Tmp')
要分两部来做: 1.删除这个临时表 if not object_id('Tmp') is null--存在删除 exec('drop table Tmp') 2.利用Select * into Tmp From 表名 来创建一个临时表第一点很重要,否则可能会无法创建临时表的情况
--感谢各位的回答,现附上测试数据, CREATE TABLE [dbo].[MaterialB] ( [MaterialID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL , [MaterialCnName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [MaterialPyCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [MaterialEnCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [UpID] [varchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL , [IsMaterial] [tinyint] NOT NULL , [StopFlag] [tinyint] NOT NULL ) ON [PRIMARY] GOinsert into materialb select 'M0101','铁精粉', 'tjf','M01',0,0 insert into materialb select 'M010101','全铁', 'qt','TFe','M0101',1,0 insert into materialb select 'M010102','二氧化硅','eyhg','Si02','M0101',1,0 insert into materialb select 'M010103','三氧化二铝', 'syhel','Al2O3' ,'M0101',1,0 insert into materialb select 'M010104','氧化钙','yhg','CaO','M0101',1,0 insert into materialb select 'M0102','焦炭', 'jt','M01',0,0 insert into materialb select 'M010201','灰分','hf','Ad','M0102',1,0 insert into materialb select 'M010202','挥发分','hff','Vdaf' ,'M0102',1,0 insert into materialb select 'M010203','硫','l','Std','M0102',1,0 insert into materialb select 'M010204','水分','sf','Mt','M0102',1,0 --sql语句 declare @s varchar(4000) --DECLARE @ID VARCHAR(10) --SET @ID='M02' set @s='' select @s=@s+',sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end) as ' + '"' +MAterialcnname +'('+ +materialencode + ')"' from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a --order by materialcnname set @s='select materialid as 物料编号,materialcnname as 物料名称' +@s +' from materialb WHERE MATERIALID='''+ @ID + ''' group by materialid,materialcnname ' --print @s --这里怎么将这个动态语句写成一个临时表或表变量,我能进一步操作,最后形成最终表输出 exec(@s)
第一种方法: 把exec(@sql) 做成存储过程,再调用select * into t from openrowset('sqloledb','ip';'sa';'','set fmtonly off;exec 库名.dbo.存储过程(参数)') 第二种方法:直接在exce()写插入语句exec('select * into 表名' +@sql)
declare @s varchar(4000) DECLARE @ID VARCHAR(10) SET @ID='M0101' set @s='' select @s=@s+',sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end) as ' + '"' +MAterialcnname +'('+ +materialencode + ')"' from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a --order by materialcnname set @s='select materialid as 物料编号,materialcnname as 物料名称' +@s +' from materialb WHERE MATERIALID='''+ @ID + ''' group by materialid,materialcnname ' select @s='select * into temp ('+@s+')' --print @s exec(@s)服务器: 消息 263,级别 16,状态 1,行 1 必须指定要从中选择的表。 怎么不对呀?
这样试试: declare @s varchar(4000) DECLARE @ID VARCHAR(10) SET @ID='M0101' set @s='select materialid as 物料编号,materialcnname as 物料名称' select @s=@s+',['+ materialcnname +']=sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end)' from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a set @s=@s +' from materialb WHERE MATERIALID='''+ @ID + ''' group by materialid,materialcnname ' print @s select @s='select * into #temp from ('+@s+')c' exec(@s)
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=xxx',
'EXEC 库名.dbo.过程名 参数') AS a
declare @S varchar(8000)
create table #B(I int)
set @S = 'select Year(getdate())'
insert into #B(I)
exec(@S)
select * from #B
drop table #B----------------(所影响的行数为 1 行)
(所影响的行数为 1 行)
I
2007
set nocount on
create table test(model varchar(20),date int ,qty int)
insert into test select 'a','8','10'
insert into test select 'a','10','50'
insert into test select 'b','8','100'
insert into test select 'b','9','200'
insert into test select 'b','10','100'
insert into test select 'c','10','200'
insert into test select 'd','10','300'
insert into test select 'e','11','250'
insert into test select 'e','12','100'
insert into test select 'f','12','150'
go
--测试declare @sql varchar(8000)
declare @tablename varchar(100)
set @tablename='t_'+replace(newid(),'-','')
set @sql='select model,'
select @sql=@sql+'sum(case when date='''+cast(date as varchar(10))+''' then qty else 0 end)['+cast(date as varchar(10))+'],'
from (select distinct top 100 percent date
from test order by date)aset @sql =left(@sql,len(@sql)-1)+' into '+@tablename+' from test group by model'exec(@sql)exec ('select * from '+@tablename)
--删除测试环境
drop table test
exec ('drop table '+@tablename)
set nocount off/*
model 8 9 10 11 12
-------------------- ----------- ----------- ----------- ----------- -----------
a 10 0 50 0 0
b 100 200 100 0 0
c 0 0 200 0 0
d 0 0 300 0 0
e 0 0 0 250 100
f 0 0 0 0 150
*/
set @s = 'select ..... into ## from .......'
exec(@s)
select * from ##
drop table ##
生成一个表:declare @s nvarchar(4000)
set @s=''
select @s=...........
set @s='select '+@s+' into Tmp from T'
select * from syscolumns where ID=object_ID('T')--得到表的结构...处理结果集
--
if not object_id('Tmp') is null--存在删除
exec('drop table Tmp')
1.删除这个临时表
if not object_id('Tmp') is null--存在删除
exec('drop table Tmp')
2.利用Select * into Tmp From 表名 来创建一个临时表第一点很重要,否则可能会无法创建临时表的情况
CREATE TABLE [dbo].[MaterialB] (
[MaterialID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MaterialCnName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MaterialPyCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MaterialEnCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UpID] [varchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[IsMaterial] [tinyint] NOT NULL ,
[StopFlag] [tinyint] NOT NULL
) ON [PRIMARY]
GOinsert into materialb select 'M0101','铁精粉', 'tjf','M01',0,0
insert into materialb select 'M010101','全铁', 'qt','TFe','M0101',1,0
insert into materialb select 'M010102','二氧化硅','eyhg','Si02','M0101',1,0
insert into materialb select 'M010103','三氧化二铝', 'syhel','Al2O3' ,'M0101',1,0
insert into materialb select 'M010104','氧化钙','yhg','CaO','M0101',1,0
insert into materialb select 'M0102','焦炭', 'jt','M01',0,0
insert into materialb select 'M010201','灰分','hf','Ad','M0102',1,0
insert into materialb select 'M010202','挥发分','hff','Vdaf' ,'M0102',1,0
insert into materialb select 'M010203','硫','l','Std','M0102',1,0
insert into materialb select 'M010204','水分','sf','Mt','M0102',1,0
--sql语句
declare @s varchar(4000)
--DECLARE @ID VARCHAR(10)
--SET @ID='M02'
set @s=''
select @s=@s+',sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end) as ' + '"' +MAterialcnname +'('+ +materialencode + ')"'
from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a --order by materialcnname
set @s='select materialid as 物料编号,materialcnname as 物料名称' +@s +' from materialb WHERE MATERIALID='''+ @ID +
''' group by materialid,materialcnname '
--print @s
--这里怎么将这个动态语句写成一个临时表或表变量,我能进一步操作,最后形成最终表输出
exec(@s)
from openrowset('sqloledb','ip';'sa';'','set fmtonly off;exec 库名.dbo.存储过程(参数)')
第二种方法:直接在exce()写插入语句exec('select * into 表名' +@sql)
DECLARE @ID VARCHAR(10)
SET @ID='M0101'
set @s=''
select @s=@s+',sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end) as ' + '"' +MAterialcnname +'('+ +materialencode + ')"'
from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a --order by materialcnname
set @s='select materialid as 物料编号,materialcnname as 物料名称' +@s +' from materialb WHERE MATERIALID='''+ @ID +
''' group by materialid,materialcnname '
select @s='select * into temp ('+@s+')'
--print @s
exec(@s)服务器: 消息 263,级别 16,状态 1,行 1
必须指定要从中选择的表。
怎么不对呀?
这样试试:
declare @s varchar(4000)
DECLARE @ID VARCHAR(10)
SET @ID='M0101'
set @s='select materialid as 物料编号,materialcnname as 物料名称'
select @s=@s+',['+ materialcnname +']=sum(case materialcnname when '''+materialcnname+''' then 0.0000 else 0.0000 end)'
from ( select materialcnname ,materialencode from materialb where upid= @id and stopflag=0)a
set @s=@s +' from materialb WHERE MATERIALID='''+ @ID + ''' group by materialid,materialcnname '
print @s
select @s='select * into #temp from ('+@s+')c'
exec(@s)