temporanee - t sql table variable in stored procedure




Inserire i risultati di una stored procedure in una tabella temporanea (17)

Come si fa a SELECT * INTO [temp table] FROM [stored procedure] ? Non da FROM [Table] e senza definire [temp table] ?

Select tutti i dati da BusinessLine in tmpBusLine funziona tmpBusLine .

select *
into tmpBusLine
from BusinessLine

Sto provando lo stesso, ma usando una stored procedure che restituisce i dati, non è esattamente la stessa cosa.

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

Messaggio di uscita:

Messaggio 156, livello 15, stato 1, riga 2 Sintassi non corretta accanto alla parola chiave "exec".

Ho letto diversi esempi di creazione di una tabella temporanea con la stessa struttura della stored procedure di output, che funziona bene, ma sarebbe bello non fornire alcuna colonna.


La soluzione più semplice:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Se non conosci lo schema, puoi fare quanto segue. Si prega di notare che ci sono gravi rischi per la sicurezza in questo metodo.

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

  1. Sto creando una tabella con il seguente schema e dati.
  2. Creare una stored procedure.
  3. Ora so qual è il risultato della mia procedura, quindi sto eseguendo la seguente query.

    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] On

    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;
    

La tua stored procedure recupera solo i dati o li modifica? Se viene utilizzato solo per il recupero, è possibile convertire la stored procedure in una funzione e utilizzare le Common Table Expressions (CTE) senza doverla dichiarare, come segue:

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

Tuttavia, qualunque cosa debba essere recuperata dal CTE dovrebbe essere usata in una sola istruzione. Non si può fare a with temp as ... e provare a usarlo dopo un paio di righe di SQL. È possibile avere più CTE in un'unica istruzione per query più complesse.

Per esempio,

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)

Per inserire il primo set di record di una stored procedure in una tabella temporanea è necessario sapere quanto segue:

  1. solo il primo set di righe della stored procedure può essere inserito in una tabella temporanea
  2. la stored procedure non deve eseguire istruzioni T-SQL dinamiche ( sp_executesql )
  3. devi prima definire la struttura della tabella temporanea

Quanto sopra può sembrare una limitazione, ma IMHO ha perfettamente senso - se si utilizza sp_executesql è possibile restituire una volta due colonne e una volta dieci e se si dispone di più set di risultati, non è possibile inserirli in più tabelle - è possibile inserire massimo in due tabelle in una istruzione T-SQL (utilizzando la clausola OUTPUT e nessun trigger).

Quindi, il problema è principalmente come definire la struttura temporanea della tabella prima di eseguire l'istruzione EXEC ... INTO ...

Il primo funziona con OBJECT_ID mentre il secondo e il terzo funzionano anche con query ad hoc. Preferisco utilizzare la DMV invece della sp perché è possibile utilizzare CROSS APPLY e creare contemporaneamente definizioni di tabelle temporanee per più procedure.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

Inoltre, prestare attenzione al campo system_type_name poiché può essere molto utile. Memorizza la definizione completa della colonna. Per esempio:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

e puoi usarlo direttamente nella maggior parte dei casi per creare la definizione della tabella.

Quindi, penso che nella maggior parte dei casi (se la procedura memorizzata corrisponde a determinati criteri) puoi facilmente creare istruzioni dinamiche per risolvere tali problemi (creare la tabella temporanea, inserire il risultato della procedura memorizzata, fare ciò che ti serve con i dati) .

Si noti che gli oggetti sopra non riescono a definire i primi dati della serie di risultati in alcuni cases come quando vengono eseguite istruzioni T-SQL dinamiche o vengono utilizzate tabelle temporanee nella stored procedure.


Quando la stored procedure restituisce molte colonne e non si vuole "creare" manualmente una tabella temporanea per contenere il risultato, ho trovato che il modo più semplice è entrare nella stored procedure e aggiungere una clausola "in" sul ultima istruzione select e aggiungi 1 = 0 alla clausola where.

Esegui la stored procedure una volta e torna indietro e rimuovi il codice SQL appena aggiunto. Ora, avrai una tabella vuota che corrisponde al risultato della procedura memorizzata. Puoi "creare una tabella come creato" per una tabella temporanea o semplicemente inserirla direttamente in quella tabella.


Quassnoi mi ha messo quasi tutto lì, ma mancava una cosa:

**** Ho avuto bisogno di utilizzare i parametri nella stored procedure. ****

E OPENQUERY non consente che ciò avvenga:

Così ho trovato un modo per lavorare il sistema e anche non dover rendere la definizione della tabella così rigida, e ridefinirla in un'altra stored procedure (e ovviamente rischiare che si possa rompere)!

Sì, è possibile creare dinamicamente la definizione della tabella restituita dalla stored procedure utilizzando l'istruzione OPENQUERY con bogus varaiables (a condizione che NO RESULT SET restituisca lo stesso numero di campi e nella stessa posizione di un set di dati con dati validi).

Una volta creata la tabella, è possibile utilizzare la stored procedure exec nella tabella temporanea per tutto il giorno.

E per notare (come indicato sopra) è necessario abilitare l'accesso ai dati,

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

Codice:

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

Grazie per le informazioni fornite originariamente ... Sì, finalmente non devo creare tutte queste definizioni di tabelle false (severe) quando si utilizzano dati da un'altra stored procedure o database, e sì, è possibile utilizzare anche i parametri.

Cerca tag di riferimento:

  • SQL 2005 stored procedure nella tabella temporanea

  • openquery con stored procedure e variabili 2005

  • openquery con variabili

  • eseguire la stored procedure nella tabella temporanea

Aggiornamento: questo non funzionerà con le tabelle temporanee quindi ho dovuto ricorrere alla creazione manuale della tabella temporanea.

Avviso di bummer : non funzionerà con tabelle temporanee , http://www.sommarskog.se/share_data.html#OPENQUERY

Riferimento: la prossima cosa è definire LOCALSERVER. Può sembrare una parola chiave nell'esempio, ma in realtà è solo un nome. Ecco come lo fai:

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

Per creare un server collegato, è necessario disporre dell'autorizzazione ALTER ANY SERVER oppure essere membro di uno qualsiasi dei ruoli del server fisso sysadmin o setupadmin.

OPENQUERY apre una nuova connessione a SQL Server. Ciò ha alcune implicazioni:

La procedura che chiami con OPENQUERY non può fare riferimento a tabelle temporanee create nella connessione corrente.

La nuova connessione ha il proprio database predefinito (definito con sp_addlinkedserver, il default è master), quindi tutte le specifiche dell'oggetto devono includere un nome di database.

Se si dispone di una transazione aperta e si stanno bloccando i blocchi quando si chiama OPENQUERY, la procedura chiamata non può accedere a ciò che si blocca. Cioè, se non stai attento ti bloccerai.

Il collegamento non è gratuito, quindi c'è una penalità per le prestazioni.


Questo processo memorizzato fa il lavoro:

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

È una leggera rielaborazione di ciò: inserisci i risultati della stored procedure nella tabella in modo che funzioni effettivamente.

Se si desidera che funzioni con una tabella temporanea, sarà necessario utilizzare una tabella ##GLOBAL e rilasciarla successivamente.


Se OPENROWSET ti sta causando problemi, c'è un altro modo dal 2012 in poi; fai uso di sys.dm_exec_describe_first_result_set_for_object, come menzionato qui: recupera i nomi delle colonne e i tipi di una stored procedure?

Innanzitutto, creare questa stored procedure per generare l'SQL per il temporaneo

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 +')'

Per utilizzare la procedura, chiamala nel seguente modo:

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

Si noti che sto usando una tabella temporanea globale. Questo perché l'uso di EXEC per eseguire l'SQL dinamico crea la propria sessione, quindi una normale tabella temporanea non rientra nell'ambito di alcun codice successivo. Se una tabella temporanea globale è un problema, è possibile utilizzare una tabella temporanea ordinaria, ma qualsiasi SQL successivo deve essere dinamico, ovvero eseguito anche dall'istruzione EXEC.


Se la tabella dei risultati del processo memorizzato è troppo complicata per scrivere manualmente la dichiarazione "Crea tabella" e non è possibile utilizzare OPENQUERY O OPENROWSET, è possibile utilizzare sp_help per generare l'elenco di colonne e tipi di dati per l'utente. Una volta ottenuto l'elenco delle colonne, è solo questione di formattarlo in base alle proprie esigenze.

Passaggio 1: aggiungere "in #temp" alla query di output (ad esempio "selezionare [...] in #temp da [...]").

Il modo più semplice è modificare direttamente la query di output nel proc. se non è possibile modificare il proc memorizzato, è possibile copiare il contenuto in una nuova finestra di query e modificare la query lì.

Passaggio 2: eseguire sp_help sulla tabella temporanea. (ad esempio "exec tempdb..sp_help #temp")

Dopo aver creato la tabella temporanea, eseguire sp_help sulla tabella temporanea per ottenere un elenco di colonne e tipi di dati, inclusa la dimensione dei campi varchar.

Passaggio 3: copia le colonne e i tipi di dati in un'istruzione table create

Ho un foglio di Excel che uso per formattare l'output di sp_help in un'istruzione "create table". Non hai bisogno di nulla di interessante, basta copiare e incollare nel tuo editor SQL. Usa i nomi delle colonne, le dimensioni e i tipi per costruire una dichiarazione "Crea tabella #x [...]" o "dichiarazione @x tabella [...]" che puoi usare per INSERIRE i risultati della stored procedure.

Passaggio 4: inserire nella tabella appena creata

Ora avrai una query simile alle altre soluzioni descritte in questo thread.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

Questa tecnica può anche essere utilizzata per convertire una tabella temporanea ( #temp ) in una variabile di tabella ( @temp ). Anche se questo può essere più di un semplice processo di create table dell'istruzione create table , impedisce errori manuali come errori di battitura e mancanze di tipo di dati nei processi di grandi dimensioni. Il debug di un refuso può richiedere più tempo rispetto alla scrittura della query.


Se sei abbastanza fortunato da avere SQL 2012 o versioni successive, puoi utilizzare dm_exec_describe_first_result_set_for_object

Ho appena modificato lo sql fornito da gotqn. Grazie gotqn.

Questo crea una tabella temporanea globale con nome uguale al nome della procedura. La tabella temporanea può essere successivamente utilizzata come richiesto. Basta non dimenticare di rilasciarlo prima di rieseguire.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end

Codice

CREATE TABLE #T1
(
    col1 INT NOT NULL,
    col2 NCHAR(50) NOT NULL,
    col3 TEXT NOT NULL,
    col4 DATETIME NULL,
    col5 NCHAR(50) NULL,
    col6 CHAR(2) NULL,
    col6 NCHAR(100) NULL,
    col7 INT NULL,
    col8 NCHAR(50) NULL,
    col9 DATETIME NULL,
    col10 DATETIME NULL
)

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)


SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col6,
    col7,
    col8,
    col9,
    col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

Spero che questo possa essere d'aiuto. Si prega di qualificarsi come appropriato.


Beh, devi creare una tabella temporanea, ma non deve avere lo schema giusto .... Ho creato una procedura memorizzata che modifica una tabella temporanea esistente in modo che abbia le colonne richieste con i dati corretti digita e ordina (cancella tutte le colonne esistenti, aggiungendo nuove colonne):

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

Nota che questo non funzionerà se sys.dm_exec_describe_first_result_set_for_object non può determinare i risultati della stored procedure (ad esempio se utilizza una tabella temporanea).


Se si conoscono i parametri che vengono passati e se non si ha accesso a sp_configure, modificare la procedura memorizzata con questi parametri e la stessa può essere memorizzata in una tabella globale ##.


Ho incontrato lo stesso problema ed ecco cosa ho fatto per questo dal suggerimento di Paul . La parte principale è qui da usare NEWID()per evitare che più utenti eseguano le procedure / gli script del negozio contemporaneamente, il dolore per la tabella temporanea globale.

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 + ']')

Questo può essere fatto in SQL Server 2014+ purché SP restituisca solo una tabella. Se qualcuno trova un modo per farlo per più tavoli, mi piacerebbe saperlo.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

Ciò estrae la definizione della tabella restituita dalle tabelle di sistema e la utilizza per creare la tabella temporanea per te. È quindi possibile popolarlo dall'SP come indicato in precedenza.

Ci sono anche varianti di questo che funzionano anche con Dynamic SQL.


Un altro metodo consiste nel creare un tipo e utilizzare PIPELINED per ritrasferire l'oggetto. Questo è limitato alla conoscenza delle colonne comunque. Ma ha il vantaggio di poter fare:

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

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