sql with Fügen Sie Ergebnisse einer gespeicherten Prozedur in eine temporäre Tabelle ein




stored procedure sql server (20)

Wie mache ich eine SELECT * INTO [temp table] FROM [stored procedure] ? Nicht von FROM [Table] und ohne [temp table] ?

Select alle Daten aus BusinessLine in tmpBusLine funktioniert gut.

select *
into tmpBusLine
from BusinessLine

Ich versuche das gleiche, aber mit einer stored procedure , die Daten zurückgibt, ist nicht ganz dasselbe.

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

Ausgabenachricht:

Nachricht 156, Ebene 15, Status 1, Zeile 2 Falsche Syntax in der Nähe des Schlüsselwortes 'exec'.

Ich habe mehrere Beispiele zum Erstellen einer temporären Tabelle mit der gleichen Struktur wie die gespeicherte Ausgabeprozedur gelesen, die gut funktioniert, aber es wäre nett, keine Spalten zu liefern.


Ruft Ihre gespeicherte Prozedur die Daten nur ab oder ändert sie auch? Wenn es nur zum Abrufen verwendet wird, können Sie die gespeicherte Prozedur in eine Funktion konvertieren und die Common Table Expressions (CTEs) verwenden, ohne sie wie folgt deklarieren zu müssen:

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

Was immer aus dem CTE abgerufen werden muss, sollte jedoch nur in einer Anweisung verwendet werden. Sie können nicht with temp as ... und versuchen, es nach ein paar Zeilen von SQL zu verwenden. Sie können mehrere CTEs in einer Anweisung für komplexere Abfragen verwenden.

Beispielsweise,

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)

In SQL Server 2005 können Sie INSERT INTO ... EXEC , um das Ergebnis einer gespeicherten Prozedur in eine Tabelle einzufügen. Von MSDN INSERT Dokumentation (für SQL Server 2000 tatsächlich):

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

Um den ersten Datensatz einer gespeicherten Prozedur in eine temporäre Tabelle einzufügen, müssen Sie Folgendes wissen:

  1. Nur der erste Zeilensatz der gespeicherten Prozedur kann in eine temporäre Tabelle eingefügt werden
  2. Die gespeicherte Prozedur darf keine dynamische T-SQL-Anweisung ausführen ( sp_executesql )
  3. Sie müssen zuerst die Struktur der temporären Tabelle definieren

Das oben kann als Einschränkung aussehen, aber IMHO macht es vollkommen Sinn - wenn Sie sp_executesql , können Sie einmal zwei Spalten und einmal zehn zurückgeben, und wenn Sie mehrere Ergebnismengen haben, können Sie sie auch nicht in mehrere Tabellen einfügen - Sie können einfügen Maximum in zwei Tabellen in einer T-SQL-Anweisung (mit OUTPUT Klausel und ohne Trigger).

Das Problem besteht hauptsächlich darin, wie die temporäre Tabellenstruktur definiert wird, bevor die Anweisung EXEC ... INTO ... .

Der erste arbeitet mit OBJECT_ID der zweite und der dritte mit Ad-hoc-Abfragen. Ich bevorzuge die Verwendung des DMV anstelle von SP, da Sie CROSS APPLY und die Definitionen für temporäre Tabellen für mehrere Prozeduren gleichzeitig CROSS APPLY können.

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;

system_type_name auf das Feld system_type_name , da es sehr nützlich sein kann. Es speichert die vollständige Definition der Spalte. Beispielsweise:

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

und Sie können es direkt in den meisten Fällen verwenden, um die Tabellendefinition zu erstellen.

Also, ich denke in den meisten Fällen (wenn die gespeicherte Prozedur bestimmte Kriterien erfüllt) können Sie leicht dynamische Anweisungen erstellen, um solche Probleme zu lösen (erstellen Sie die temporäre Tabelle, fügen Sie das Ergebnis der gespeicherten Prozedur ein, tun Sie, was Sie mit den Daten brauchen) .

Beachten Sie, dass die obigen Objekte in einigen cases nicht die ersten Resultsetdaten definieren, z. B. wenn dynamische T-SQL-Anweisungen ausgeführt oder temporäre Tabellen in der gespeicherten Prozedur verwendet werden.


I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

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


Wenn die Abfrage keinen Parameter enthält, verwenden OpenQuery OpenRowset . Verwenden OpenQuery andernfalls OpenRowset .

Grundlegend wäre, ein Schema nach einer gespeicherten Prozedur zu erstellen und in diese Tabelle einzufügen. z.B:

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

Wenn Sie Glück haben, SQL 2012 oder höher zu haben, können Sie dm_exec_describe_first_result_set_for_object

Ich habe gerade das von Gotqn zur Verfügung gestellte sql bearbeitet. Danke Gotqn.

Dies erstellt eine globale temporäre Tabelle mit demselben Namen wie der Prozedurname. Die temporäre Tabelle kann später wie benötigt verwendet werden. Vergessen Sie nicht, es vor der erneuten Ausführung zu löschen.

    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

If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.


This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

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

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.


Wenn die gespeicherte Prozedur viele Spalten zurückgibt und Sie nicht möchten, dass eine temporäre Tabelle manuell erstellt wird, um das Ergebnis zu speichern, habe ich herausgefunden, dass der einfachste Weg darin besteht, in die gespeicherte Prozedur zu gehen und eine "in" -Klausel hinzuzufügen Letzte SELECT-Anweisung und fügen Sie der WHERE-Klausel 1 = 0 hinzu.

Führen Sie die gespeicherte Prozedur einmal aus und gehen Sie zurück und entfernen Sie den gerade hinzugefügten SQL-Code. Jetzt haben Sie eine leere Tabelle, die dem Ergebnis der gespeicherten Prozedur entspricht. Sie könnten entweder "Tabelle als Erstelle" für eine temporäre Tabelle erstellen oder einfach direkt in diese Tabelle einfügen.


Sie können dafür OPENROWSET . Guck mal. Ich habe auch den sp_configure-Code hinzugefügt, um Ad-hoc-verteilte Abfragen zu aktivieren, falls dies nicht bereits aktiviert ist.

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

I met the same problem and here is what I did for this from Paul's suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

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

Wenn das OPENROWSET Ihnen Probleme verursacht, gibt es ab 2012 einen anderen Weg. Verwenden Sie sys.dm_exec_describe_first_result_set_for_object, wie hier erwähnt: Abrufen von Spaltennamen und Typen einer gespeicherten Prozedur?

Erstellen Sie zuerst diese gespeicherte Prozedur, um das SQL für das temporäre zu generieren

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

Um das Verfahren zu verwenden, rufen Sie es wie folgt auf:

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

Beachten Sie, dass ich eine globale temporäre Tabelle verwende. Das liegt daran, dass die Verwendung von EXEC zum Ausführen des dynamischen SQL-Befehls eine eigene Sitzung erstellt, sodass eine normale temporäre Tabelle nicht mehr für den nachfolgenden Code verwendet werden kann. Wenn eine globale temporäre Tabelle ein Problem darstellt, können Sie eine normale temporäre Tabelle verwenden, aber jedes nachfolgende SQL muss dynamisch sein, dh auch von der EXEC-Anweisung ausgeführt werden.


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;

Wenn Sie dies tun möchten, ohne zuerst die temporäre Tabelle zu deklarieren, können Sie versuchen, eine benutzerdefinierte Funktion anstelle einer gespeicherten Prozedur zu erstellen und diese benutzerdefinierte Funktion dazu veranlassen, eine Tabelle zurückzugeben. Wenn Sie die gespeicherte Prozedur verwenden möchten, versuchen Sie alternativ Folgendes:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Ich habe Passing Arrays / DataTables in Stored Procedures gefunden, die Ihnen eine andere Idee geben könnten, wie Sie Ihr Problem lösen könnten.

Die Verknüpfung schlägt vor, einen Bildtyp- Parameter zu verwenden, um in der gespeicherten Prozedur zu übergeben. In der gespeicherten Prozedur wird das Bild dann in eine Tabellenvariable umgewandelt, die die ursprünglichen Daten enthält.

Vielleicht gibt es eine Möglichkeit, wie dies mit einer temporären Tabelle verwendet werden kann.


SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')

Wenn die Ergebnistabelle Ihres gespeicherten Proc zu kompliziert ist, um die Anweisung "create table" manuell einzugeben, und Sie OPENQUERY OR OPENROWSET nicht verwenden können, können Sie sp_help verwenden, um die Liste der Spalten und Datentypen für Sie zu generieren. Sobald Sie die Liste der Spalten haben, ist es nur eine Frage der Formatierung für Ihre Bedürfnisse.

Schritt 1: Fügen Sie "#temp" zur Ausgabeanfrage hinzu (zB "wählen Sie [...] in #temp from [...]").

Der einfachste Weg ist, die Ausgabeabfrage direkt im proc zu bearbeiten. Wenn Sie das gespeicherte Proc nicht ändern können, können Sie den Inhalt in ein neues Abfragefenster kopieren und die Abfrage dort ändern.

Schritt 2: Führen Sie sp_help für die temporäre Tabelle aus. (zB "exec tempdb ... sp_help #temp")

Führen Sie nach dem Erstellen der temporären Tabelle sp_help für die temporäre Tabelle aus, um eine Liste der Spalten und Datentypen einschließlich der Größe der varchar-Felder abzurufen.

Schritt 3: Kopieren Sie die Datenspalten und -typen in eine create table-Anweisung

Ich habe ein Excel-Blatt, das ich verwende, um die Ausgabe von sp_help in eine "create table" -Anweisung zu formatieren. Sie brauchen nichts, was Ihnen gefällt, kopieren Sie einfach und fügen Sie sie in Ihren SQL-Editor ein. Verwenden Sie die Spaltennamen, Größen und Typen, um eine Anweisung "Create table #x [...]" oder "declare @x table [...]" zu erstellen, mit der Sie die Ergebnisse der gespeicherten Prozedur einfügen können.

Schritt 4: In die neu erstellte Tabelle einfügen

Jetzt haben Sie eine Abfrage, die den anderen in diesem Thread beschriebenen Lösungen entspricht.

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

INSERT INTO @t 
Exec spMyProc 

Diese Technik kann auch verwendet werden, um eine temporäre Tabelle ( #temp ) in eine Tabellenvariable ( @temp ) zu @temp . Während dies mehr Schritte sein kann, als nur die create table Anweisung selbst zu schreiben, werden manuelle Fehler wie Tippfehler und fehlende Datentypen in großen Prozessen verhindert. Das Debuggen eines Tippfehlers kann mehr Zeit in Anspruch nehmen als das Schreiben der Abfrage an erster Stelle.


  1. Ich erstelle eine Tabelle mit dem folgenden Schema und den folgenden Daten.
  2. Erstellen Sie eine gespeicherte Prozedur.
  3. Jetzt weiß ich, was das Ergebnis meiner Prozedur ist, also führe ich die folgende Abfrage durch.

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

Dieser gespeicherte Proc erledigt die Aufgabe:

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

Es ist eine kleine Überarbeitung von dies: Einfügen von gespeicherten Prozedur Ergebnisse in Tabelle, so dass es tatsächlich funktioniert.

Wenn Sie möchten, dass es mit einer temporären Tabelle arbeitet, müssen Sie eine Tabelle ##GLOBAL und sie anschließend ##GLOBAL .


Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

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




stored-procedures