uma - tabela temporaria em view sql server




Inserir resultados de um procedimento armazenado em uma tabela temporária (17)

Solução mais fácil:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Se você não conhece o esquema, você pode fazer o seguinte. Por favor, note que existem riscos de segurança graves neste método.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

Como eu faço um SELECT * INTO [temp table] FROM [stored procedure] ? Não FROM [Table] e sem definir [temp table] ?

Select todos os dados do BusinessLine em tmpBusLine funciona bem.

select *
into tmpBusLine
from BusinessLine

Eu estou tentando o mesmo, mas usando um stored procedure que retorna dados, não é exatamente o mesmo.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

Mensagem de saída:

Msg 156, Level 15, State 1, Line 2 Sintaxe incorreta perto da palavra-chave 'exec'.

Eu li vários exemplos de criação de uma tabela temporária com a mesma estrutura que o procedimento armazenado de saída, que funciona bem, mas seria bom não fornecer colunas.


  1. Estou criando uma tabela com o seguinte esquema e dados.
  2. Crie um procedimento armazenado.
  3. Agora eu sei qual é o resultado do meu procedimento, então estou executando a consulta a seguir.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
    CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
    
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF

    VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] ativado

    create procedure GetDate
    as
    begin
        select Id,ParentId from tblTestingTree
    end
    
    create table tbltemp
    (
        id int,
        ParentId int
    )
    insert into tbltemp
    exec GetDate
    
    select * from tbltemp;

Este procedimento armazenado faz o trabalho:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

É um pequeno retrabalho: Insira os resultados do procedimento armazenado na tabela para que ele realmente funcione.

Se você quer que ele trabalhe com uma tabela temporária, então você precisará usar uma tabela ##GLOBAL e soltá-la depois.


No SQL Server 2005, você pode usar INSERT INTO ... EXEC para inserir o resultado de um procedimento armazenado em uma tabela. Da documentação INSERT do MSDN (para o SQL Server 2000, na verdade):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

Quando o procedimento armazenado retorna um monte de colunas e você não deseja "criar" manualmente uma tabela temporária para manter o resultado, descobri que a maneira mais fácil é entrar no procedimento armazenado e adicionar uma cláusula "into" no última instrução select e adicione 1 = 0 à cláusula where.

Execute o procedimento armazenado uma vez e volte e remova o código SQL que você acabou de adicionar. Agora, você terá uma tabela vazia correspondente ao resultado do procedimento armazenado. Você poderia "criar tabela de script como criar" para uma tabela temporária ou simplesmente inserir diretamente nessa tabela.


Quassnoi me colocou a maior parte do caminho, mas uma coisa estava faltando:

**** Eu precisava usar parâmetros no procedimento armazenado. ****

E OPENQUERY não permite que isso aconteça:

Então eu encontrei uma maneira de trabalhar o sistema e também não ter que tornar a definição da tabela tão rígida, e redefini-la dentro de outro procedimento armazenado (e, claro, ter a chance de que ela possa quebrar)!

Sim, você pode criar dinamicamente a definição de tabela retornada do procedimento armazenado usando a instrução OPENQUERY com varias variáveis ​​falsas (contanto que o NO RESULT SET retorne o mesmo número de campos e na mesma posição que um conjunto de dados com dados bons).

Depois que a tabela é criada, você pode usar o procedimento armazenado exec na tabela temporária durante todo o dia.

E para notar (como indicado acima), você deve ativar o acesso a dados,

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

Código:

declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
  'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

Obrigado pela informação que foi fornecida originalmente ... Sim, finalmente eu não tenho que criar todas essas definições de tabela falsa (estrita) ao usar dados de outro procedimento armazenado ou banco de dados, e sim você pode usar parâmetros também.

Pesquisar tags de referência:

  • Procedimento armazenado do SQL 2005 na tabela temporária

  • openquery com procedimento armazenado e variáveis ​​2005

  • openquery com variáveis

  • executar o procedimento armazenado na tabela temporária

Update: isso não funcionará com tabelas temporárias, então tive que recorrer à criação manual da tabela temporária.

Não há problema : isso não funcionará com tabelas temporárias , http://www.sommarskog.se/share_data.html#OPENQUERY

Referência: O próximo passo é definir LOCALSERVER. Pode parecer uma palavra-chave no exemplo, mas na verdade é apenas um nome. É assim que se faz:

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

Para criar um servidor vinculado, você deve ter a permissão ALTER ANY SERVER ou ser um membro de qualquer uma das funções de servidor fixas sysadmin ou setupadmin.

OPENQUERY abre uma nova conexão com o SQL Server. Isso tem algumas implicações:

O procedimento que você chama com OPENQUERY não pode referenciar tabelas temporárias criadas na conexão atual.

A nova conexão possui seu próprio banco de dados padrão (definido com sp_addlinkedserver, o padrão é master), portanto, toda especificação de objeto deve incluir um nome de banco de dados.

Se você tem uma transação aberta e está segurando bloqueios quando você chama OPENQUERY, o procedimento chamado não pode acessar o que você bloqueia. Isto é, se você não for cuidadoso, você se bloqueará.

Conectar não é de graça, então há uma penalidade de desempenho.


Se o OPENROWSET está causando problemas, existe outro caminho a partir de 2012; faça uso de sys.dm_exec_describe_first_result_set_for_object, como mencionado aqui: Recuperar nomes de colunas e tipos de um procedimento armazenado?

Primeiro, crie este procedimento armazenado para gerar o SQL para o temporário

CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
    @ProcedureName  nvarchar(128),
    @TableName      nvarchar(128),
    @SQL            nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +''  + ','
        FROM sys.dm_exec_describe_first_result_set_for_object
        (
          OBJECT_ID(@ProcedureName), 
          NULL
        );

--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))    
SET @SQL =  @SQL +')'

Para usar o procedimento, chame da seguinte maneira:

DECLARE     @SQL    NVARCHAR(MAX)

exec dbo.usp_GetStoredProcTableDefinition
    @ProcedureName='dbo.usp_YourProcedure',
    @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC    [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable

Observe que estou usando uma tabela temporária global. Isso porque usar o EXEC para executar o SQL dinâmico cria sua própria sessão, portanto, uma tabela temporária comum estaria fora do escopo para qualquer código subseqüente. Se uma tabela temporária global é um problema, você pode usar uma tabela temporária comum, mas qualquer SQL subseqüente precisaria ser dinâmico, isto é, também executado pela instrução EXEC.


Se você quiser fazer isso sem primeiro declarar a tabela temporária, você pode tentar criar uma função definida pelo usuário em vez de um procedimento armazenado e fazer com que essa função definida pelo usuário retorne uma tabela. Alternativamente, se você quiser usar o procedimento armazenado, tente algo como isto:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Seu procedimento armazenado apenas recupera os dados ou os modifica também? Se for usado apenas para recuperação, você poderá converter o procedimento armazenado em uma função e usar as Common Table Expressions (CTEs), sem precisar declará-lo, da seguinte maneira:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

No entanto, o que precisa ser recuperado do CTE deve ser usado em apenas uma instrução. Você não pode fazer um with temp as ... e tentar usá-lo depois de algumas linhas de SQL. Você pode ter vários CTEs em uma instrução para consultas mais complexas.

Por exemplo,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

Você pode usar o OPENROWSET para isso. Dar uma olhada. Eu também incluí o código sp_configure para habilitar Consultas Distribuídas Ad Hoc, caso ainda não esteja habilitado.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Bem, você precisa criar uma tabela temporária, mas ela não precisa ter o esquema correto ... Eu criei um procedimento armazenado que modifica uma tabela temporária existente para que ela tenha as colunas necessárias com os dados corretos. digite e ordene (descartando todas as colunas existentes, adicionando novas colunas):

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Observe que isso não funcionará se sys.dm_exec_describe_first_result_set_for_object não puder determinar os resultados do procedimento armazenado (por exemplo, se ele usar uma tabela temporária).


Eu faria o seguinte

  1. Crie (convert SP to) um UDF (valor de tabela UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


Eu encontrei Passando Arrays / DataTables em Stored Procedures, que pode lhe dar uma outra idéia de como você pode resolver o seu problema.

O link sugere usar um Imagem parâmetro do tipo para passar para o procedimento armazenado. Em seguida, no procedimento armazenado, a imagem é transformada em uma variável de tabela contendo os dados originais.

Talvez exista uma maneira de isso ser usado com uma tabela temporária.


Eu encontrei o mesmo problema e aqui está o que eu fiz para isso por sugestão de Paulo . A parte principal aqui é usar NEWID()para evitar que vários usuários executem os procedimentos / scripts da loja ao mesmo tempo, a dor da tabela temporária global.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

Outro método é criar um tipo e usar PIPELINED para passar de volta seu objeto. Isso é limitado a conhecer as colunas no entanto. Mas tem a vantagem de poder fazer:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))

Se a consulta não contiver parâmetro, use OpenQueryelse use OpenRowset.

O básico seria criar esquema de acordo com o procedimento armazenado e inserir nessa tabela. por exemplo:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;




stored-procedures