Insert results of a stored procedure into a temporary table

Question

How do I do a SELECT   INTO  temp table  FROM  stored procedure   Not FROM  Table  and without defining  temp table    Select all data from BusinessLine into tmpBusLine works fine   select   into tmpBusLine from BusinessLine   I am trying the same  but using a stored procedure that returns data  is not quite the same   select   into tmpBusLine from exec getBusinessLineHistory  16 Mar 2009    Output message      Msg 156  Level 15  State 1  Line 2   Incorrect syntax near the keyword    exec     I have read several examples of creating a temporary table with the same structure as the output stored procedure  which works fine  but it would be nice to not supply any columns

User · Answer

I found Passing Arrays DataTables into Stored Procedures which might give you another idea on how you might go solving your problem   The link suggests to use an Image type parameter to pass into the stored procedure  Then in the stored procedure  the image is transformed into a table variable containing the original data   Maybe there is a way this can be used with a temporary table

User · Answer

I m creating a table with the following schema and data   Create a stored procedure   Now I know what the result of my procedure is  so I am performing the following 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

User · Answer

If you want to do it without first declaring the temporary table  you could try creating a user-defined function rather than a stored procedure and make that user-defined function return a table  Alternatively  if you want to use the stored procedure  try something like this  CREATE TABLE  tmpBus      COL1 INT     COL2 INT    INSERT INTO  tmpBus Exec SpGetRecords  Params

User · Answer

It s a simple 2 step process    - create a temporary table  - Insert into the temporary table   Code to perform the same    CREATE TABLE  tempTable  Column1 int  Column2 varchar max    INSERT INTO  tempTable  EXEC  app   Sproc name   param1   1   param2  2

User · Answer

Does your stored procedure only retrieve the data or modify it too  If it s used only for retrieving  you can convert the stored procedure into a function and use the Common Table Expressions  CTEs  without having to declare it  as follows   with temp as       select   from dbo fnFunctionName 10  20    select col1  col2 from temp   However  whatever needs to be retrieved from the CTE should be used in one statement only  You cannot do a with temp as     and try to use it after a couple of lines of SQL  You can have multiple CTEs in one statement for more complex queries   For example   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

User · Answer

In order to insert the first record set of a stored procedure into a temporary table you need to know the following    only the first row set of the stored procedure can be inserted into a temporary table the stored procedure must not execute dynamic T-SQL statement  sp executesql  you need to define the structure of the temporary table first   The above may look as limitation  but IMHO it perfectly makes sense - if you are using sp executesql you can once return two columns and once ten  and if you have multiple result sets  you cannot insert them into several tables as well - you can insert maximum in two table in one T-SQL statement  using OUTPUT clause and no triggers    So  the issue is mainly how to define the temporary table structure before performing the EXEC     INTO     statement    sys dm exec describe first result set for object sys dm exec describe first result set sp describe first result set   The first works with OBJECT ID while the second and the third works with Ad-hoc queries as well  I prefer to use the DMV instead of the sp as you can use CROSS APPLY and build the temporary table definitions for multiple procedures at the same time   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    Also  pay attention to the system type name field as it can be very useful  It stores the column complete definition  For  example   smalldatetime nvarchar max  uniqueidentifier nvarchar 1000  real smalldatetime decimal 18 2    and you can use it directly in most of the cases to create the table definition   So  I think in most of the cases  if the stored procedure match certain criteria  you can easily build dynamic statements for solving such issues  create the temporary table  insert the stored procedure result in it  do what you need with the data      Note  that the objects above fail to define the first result set data in some cases like when dynamic T-SQL statements are executed or temporary tables are used in the stored procedure

User · Answer

If the query doesn t contain parameter  use OpenQuery else use OpenRowset   Basic thing would be to create schema as per stored procedure and insert into that table  e g    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

User · Answer

This is an answer to a slightly modified version of your question  If you can abandon the use of a stored procedure for a user-defined function  you can use an inline table-valued user-defined function  This is essentially a stored procedure  will take parameters  that returns a table as a result set  and therefore will place nicely with an INTO statement     Here s a good quick article on it and other user-defined functions   If you still have a driving need for a stored procedure  you can wrap the inline table-valued user-defined function with a stored procedure  The stored procedure just passes parameters when it calls select   from the inline table-valued user-defined function     So for instance  you d have an inline table-valued user-defined function to get a list of customers for a particular region   CREATE FUNCTION CustomersByRegion           RegionID int     RETURNS TABLE  AS RETURN    SELECT     FROM customers   WHERE RegionID    RegionID GO   You can then call this function to get what your results a such   SELECT   FROM CustomersbyRegion 1    Or to do a SELECT INTO   SELECT   INTO CustList FROM CustomersbyRegion 1    If you still need a stored procedure  then wrap the function as such   CREATE PROCEDURE uspCustomersByRegion           regionID int     AS BEGIN      SELECT   FROM CustomersbyRegion  regionID   END GO   I think this is the most  hack-less  method to obtain the desired results   It uses the existing features as they were intended to be used without additional complications   By nesting the inline table-valued user-defined function in the stored procedure  you have access to the functionality in two ways   Plus   You have only one point of maintenance for the actual SQL code   The use of OPENROWSET has been suggested  but this is not what the OPENROWSET function was intended to be used for  From Books Online       Includes all connection information   that is required to access remote data   from an OLE DB data source  This   method is an alternative to accessing   tables in a linked server and is a   one-time  ad hoc method of connecting   and accessing remote data by using OLE   DB  For more frequent references to   OLE DB data sources  use linked   servers instead    Using OPENROWSET will get the job done  but it will incur some additional overhead for opening up local connections and marshalling data   It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired   Also  the OPENROWSET approach will preclude the use of stored procedures returning more than one result set   Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this

User · Answer

Code  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   I hope this helps  Please qualify as appropriate

User · Answer

Here is my T-SQL with parameters --require one time execution if not configured before sp configure  Show Advanced Options   1 GO RECONFIGURE GO  --require one time execution if not configured before sp configure  Ad Hoc Distributed Queries   1 GO RECONFIGURE GO   --the query DECLARE  param1 int   1   param2 int   2 DECLARE  SQLStr varchar max     SELECT   INTO  MyTempTable                                 FROM OPENROWSET   SQLNCLI        Server ServerName Database DbName Trusted Connection yes      exec StoredProcedureName    CAST  param1 AS varchar 15         CAST  param2 AS varchar 15         AS a    select   from  MyTempTable   drop table  MyTempTable            EXECUTE  SQLStr

User · Answer

I would do the following   Create  convert SP to  a UDF  Table value UDF   select   into  tmpBusLine from dbo UDF getBusinessLineHistory  16 Mar 2009

User · Answer

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

User · Answer

If the results table of your stored proc is too complicated to type out the  create table  statement by hand  and you can t use OPENQUERY OR OPENROWSET  you can use sp help to generate the list of columns and data types for you   Once you have the list of columns  it s just a matter of formatting it to suit your needs     Step 1  Add  into  temp  to the output query  e g   select       into  temp from             The easiest way is to edit the output query in the proc directly   if you can t change the stored proc  you can copy the contents into a new query window and modify the query there     Step 2  Run sp help on the temp table    e g   exec tempdb  sp help  temp    After creating the temp table  run sp help on the temp table to get a list of the columns and data types including the size of varchar fields      Step 3  Copy the data columns  amp  types into a create table statement   I have an Excel sheet that I use to format the output of sp help into a  create table  statement   You don t need anything that fancy  just copy and paste into your SQL editor   Use the column names  sizes  and types to construct a  Create table  x        or  declare  x table         statement which you can use to INSERT the results of the stored procedure     Step 4  Insert into the newly created table  Now you ll have a query that s like the other solutions described in this thread     DECLARE  t TABLE       --these columns were copied from sp help    COL1 INT     COL2 INT       INSERT INTO  t  Exec spMyProc    This technique can also be used to convert a temp table   temp  to a table variable   temp    While this may be more steps than just writing the create table statement yourself  it prevents manual error such as typos and data type mismatches in large processes   Debugging a typo can take more time than writing the query in the first place

User · Answer

In SQL Server 2005 you can use INSERT INTO     EXEC to insert the result of a stored procedure into a table  From MSDN s INSERT documentation  for SQL Server 2000  in fact    --INSERT   EXECUTE procedure example INSERT author sales EXECUTE get author sales

User · Answer

A few years late to the question  but I needed something like this for some quick and dirty code generation   I believe as others have stated it is just easier to define the temp table up front  but this method should work for simple stored procedure queries or sql statments   This will be a little convoluted  but it borrows from the contributors here as well as Paul White s solution from DBA Stack Exchange Get stored procedure result column-types   Again  to reiterate this approach  amp  example is not designed for processes in a multi user environment   In this case the table definition is being set for a short time in a global temp table for reference by a code generation template process   I haven t fully tested this so there may be caveats so you may want to go to the MSDN link in Paul White s answer   This applies to SQL 2012 and higher   First use the stored procedure sp describe first result set which resembles Oracle s describe     This will evaluate the first row of the first result set so if your stored procedure or statement returns multiple queries it will only describe the first result   I created a stored proc to break down the tasks that returns a single field to select from to create the temp table definition   CREATE OR ALTER PROCEDURE  dbo   sp GetTableDefinitionFromSqlBatch DescribeFirstResultSet          sql NVARCHAR 4000        table name VARCHAR 100        TableDefinition NVARCHAR MAX  OUTPUT   AS BEGIN     SET NOCOUNT ON     DECLARE  TempTableDefinition NVARCHAR MAX      DECLARE  NewLine NVARCHAR 4    CHAR 13  CHAR 10       DECLARE  ResultDefinition TABLE    --The View Definition per MSDN       is hidden         bit NOT NULL       column ordinal    int NOT NULL        name             sysname NULL       is nullable       bit NOT NULL       system type id    int NOT NULL       system type name  nvarchar 256  NULL       max length        smallint NOT NULL        precision        tinyint NOT NULL       scale             tinyint NOT NULL       collation name    sysname NULL           user type id      int NULL       user type database    sysname NULL           user type schema  sysname NULL       user type name    sysname NULL           assembly qualified type name      nvarchar 4000          xml collection id         int NULL       xml collection database   sysname NULL           xml collection schema     sysname NULL           xml collection name       sysname NULL       is xml document           bit NOT NULL                   is case sensitive         bit NOT NULL                   is fixed length clr type  bit NOT NULL           source server             sysname NULL                   source database           sysname NULL       source schema             sysname NULL       source table              sysname NULL       source column             sysname NULL       is identity column        bit NULL       is part of unique key     bit NULL       is updateable             bit NULL       is computed column        bit NULL       is sparse column set      bit NULL       ordinal in order by list  smallint NULL          order by is descending    smallint NULL          order by list length      smallint NULL       tds type id               int NOT NULL       tds length                int NOT NULL       tds collation id          int NULL       tds collation sort id     tinyint NULL            --Insert the description into table variable         INSERT  ResultDefinition     EXEC sp describe first result set  sql      --Now Build the string to create the table via union select statement      WITH STMT AS           SELECT N CREATE TABLE      table name   N     AS TextVal         UNION ALL          SELECT           CONCAT                  CASE column ordinal                     WHEN 1 THEN         ELSE         END  --Determines if comma should precede                   QUOTENAME  name            system type name  -- Column Name and SQL TYPE                  CASE is nullable                      WHEN 0 THEN     NOT NULL  ELSE     NULL  END --NULLABLE CONSTRAINT                            AS TextVal         FROM  ResultDefinition WHERE is hidden   0  -- May not be needed         UNION ALL          SELECT N        NewLine             --Now Combine the rows to a single String     SELECT  TempTableDefinition   COALESCE   TempTableDefinition    NewLine   TextVal  TextVal  FROM STMT      SELECT  TableDefinition    TempTableDefinition END   The conundrum is that you need to use a global table  but you need to make it unique enough so you can drop and create from it frequently without worrying about a collision  In the example I used a Guid  FE264BF5 9C32 438F 8462 8A5DC8DEE49E  for the global variable replacing the hyphens with underscore  DECLARE  sql NVARCHAR 4000    N SELECT   SERVERNAME as ServerName  GETDATE   AS Today   DECLARE  GlobalTempTable VARCHAR 100    N   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable   -- sql can be a stored procedure name like dbo foo without parameters  DECLARE  TableDef NVARCHAR MAX   DROP TABLE IF EXISTS  MyTempTable DROP TABLE IF EXISTS   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable  EXEC  dbo   sp GetTableDefinitionFromSqlBatch DescribeFirstResultSet        sql   GlobalTempTable   TableDef OUTPUT  --Creates the global table   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable EXEC sp executesql  TableDef   --Now Call the stored procedure  SQL Statement with Params etc  INSERT   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable     EXEC sp executesql  sql   --Select the results into your undefined Temp Table from the Global Table SELECT    INTO  MyTempTable FROM   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable  SELECT   FROM  MyTempTable  DROP TABLE IF EXISTS  MyTempTable DROP TABLE IF EXISTS   FE264BF5 9C32 438F 8462 8A5DC8DEE49E MyTempTable   Again  I have only tested it with simple stored procedure queries and simple queries so your mileage may vary   Hope this helps someone

User · Answer

This can be done in SQL Server 2014  provided the stored procedure only returns one table  If anyone finds a way of doing this for multiple tables I d love to know about it   DECLARE  storedProcname NVARCHAR MAX       SET  storedProcname    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  storedProcname  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 stored procedure as stated before   There are also variants of this that work with Dynamic SQL too

User · Answer

After searching around I found a way to create a temp table dynamically for any stored procedure without using OPENROWSET or OPENQUERY using a generic schema of Stored Procedure s result definition especially when you are not database Administrator    Sql server has a buit-in proc sp describe first result set that can provide you with schema of any procedures resultset  I created a schema table from results of this procedure and manually set all the field to NULLABLE   declare  procname varchar 100     PROCEDURENAME  -- your procedure name declare  param varchar max       2019-06-06    -- your parameters  declare  execstr nvarchar max    N exec      procname declare  qry nvarchar max   -- Schema table to store the result from sp describe first result set  create table  d  is hidden  bit  NULL  column ordinal   int  NULL  name sysname NULL  is nullable   bit  NULL  system type id   int  NULL  system type name nvarchar 256  NULL  max length  smallint  NULL  precision   tinyint  NULL   scale   tinyint  NULL   collation name  sysname NULL  user type id  int NULL  user type database    sysname NULL  user type schema    sysname NULL user type name sysname NULL assembly qualified type name   nvarchar 4000  xml collection id    int NULL xml collection database    sysname NULL  xml collection schema   sysname NULL xml collection name    sysname NULL is xml document    bit  NULL is case sensitive bit  NULL is fixed length clr type  bit  NULL  source server   sysname NULL source database    sysname NULL source schema  sysname NULL source table   sysname NULL source column  sysname NULL is identity column bit NULL  is part of unique key   bit NULL is updateable  bit NULL is computed column bit NULL is sparse column set   bit NULL ordinal in order by list   smallint NULL  order by list length    smallint NULL order by is descending    smallint NULL tds type id   int  NULL tds length    int  NULL tds collation id  int NULL  tds collation sort id   tinyint NULL    -- Get result set definition of your procedure insert into  d EXEC sp describe first result set  exestr  NULL  0  -- Create a query to generate and populate a global temp table from above results select   qry    Create table   t     stuff         select       name        system type name     NULL      from  d d For XML Path  TYPE       value N   1    N nvarchar max      1 1           insert into   t  Exec    procname        param  Exec sp executesql  qry  -- Use below global temp table to query the data as you may select   from   t  --   WARNING   Don t forget to drop the global temp table   t  --drop table   t drop table  d       Developed and tested on Sql Server version - Microsoft SQL Server 2016  RTM  - 13 0 1601 5 Build 17134      You can tweak the schema for your SQL server version that you are using  if needed

User · Answer

Quassnoi put me most of the way there  but one thing was missing       I needed to use parameters in the stored procedure       And OPENQUERY does not allow for this to happen   So I found a way to work the system and also not have to make the table definition so rigid  and redefine it inside another stored procedure  and of course take the chance it may break    Yes  you can dynamically create the table definition returned from the stored procedure by using the OPENQUERY statement with bogus varaiables  as long the NO RESULT SET returns the same number of fields and in the same position as a dataset with good data    Once the table is created  you can use exec stored procedure into the temporary table all day long     And to note  as indicated above  you must enable data access   EXEC sp serveroption  MYSERVERNAME    DATA ACCESS   TRUE     Code   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   Thanks for the information which was provided originally    Yes  finally I do not have to create all these bogus  strict  table defintions when using data from another stored procedure or database  and yes you can use parameters too   Search reference tags    SQL 2005 stored procedure into temp table openquery with stored procedure and variables 2005 openquery with variables execute stored procedure into temp table   Update  this will not work with temporary tables so I had to resort to manually creating the temporary table   Bummer notice  this will not work with temporary tables  http   www sommarskog se share data html OPENQUERY  Reference  The next thing is to define LOCALSERVER  It may look like a keyword in the example  but it is in fact only a name  This is how you do it   sp addlinkedserver  server    LOCALSERVER     srvproduct                           provider    SQLOLEDB    datasrc     servername   To create a linked server  you must have the permission ALTER ANY SERVER  or be a member of any of the fixed server roles sysadmin or setupadmin   OPENQUERY opens a new connection to SQL Server  This has some implications   The procedure that you call with OPENQUERY cannot refer temporary tables created in the current connection   The new connection has its own default database  defined with sp addlinkedserver  default is master   so all object specification must include a database name   If you have an open transaction and are holding locks when you call OPENQUERY  the called procedure can not access what you lock  That is  if you are not careful you will block yourself   Connecting is not for free  so there is a performance penalty

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Well  you do have to create a temp table  but it doesn t have to have the right schema    I ve created a stored procedure that modifies an existing temp table so that it has the required columns with the right data type and order  dropping all existing columns  adding new columns    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   Note this won t work if sys dm exec describe first result set for object can t determine the results of the stored procedure  for instance if it uses a temp table

User · Answer

If you re lucky enough to have SQL 2012 or higher  you can use dm exec describe first result set for object  I have just edited the sql provided by gotqn  Thanks gotqn   This creates a global temp table with name same as procedure name  The temp table can later be used as required  Just don t forget to drop it before re-executing       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

User · Answer

When the stored procedure returns a lot of columns and you do not want to manually  create  a temporary table to hold the result  I ve found the easiest way is to go into the stored procedure and add an  into  clause on the last select statement and add 1 0 to the where clause   Run the stored procedure once and go back and remove the SQL code you just added  Now  you ll have an empty table matching the stored procedure s result  You could either  script table as create  for a temporary table or simply insert directly into that table

User · Answer

EXEC sp serveroption  YOURSERVERNAME    DATA ACCESS   TRUE  SELECT    INTO     tmpTable FROM    OPENQUERY YOURSERVERNAME   EXEC db schema sproc 1

User · Answer

Easiest Solution    CREATE TABLE  temp         INSERT INTO  temp EXEC  sproc      If you don t know the schema then you can do the following  Please note that there are severe security risks in this method   SELECT    INTO  temp FROM OPENROWSET  SQLNCLI                     Server localhost Trusted Connection yes                      EXEC  db   schema   sproc

User · Answer

This stored proc does the job   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   It s a slight rework of this  Insert stored procedure results into table so that it actually works   If you want it to work with a temporary table then you will need to use a   GLOBAL table and drop it afterwards

User · Answer

If the OPENROWSET is causing you issues  there is another way from 2012 onwards  make use of sys dm exec describe first result set for object  as mentioned here  Retrieve column names and types of a stored procedure   First  create this stored procedure to generate the SQL for the temporary table   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        To use the procedure  call it in the following way   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   Note that I m using a global temporary table  That s because using EXEC to run the dynamic SQL creates its own session  so an ordinary temporary table would be out of scope to any subsequent code  If a global temporary table is a problem  you can use an ordinary temporary table  but any subsequent SQL would need to be dynamic  that is  also executed by the EXEC statement

User · Answer

If you let dynamic SQL create a temp table  this table is owned by the Dynamic SQL connection  as opposed to the connection your stored procedure is called from   DECLARE  COMMA SEPARATED KEYS varchar MAX   DROP TABLE IF EXISTS KV  CREATE TABLE KV  id person int  mykey varchar 30   myvalue int   INSERT INTO KV VALUES  1   age   16    1   weight   63    1   height   175    2   age   26    2   weight   83    2   height   185   WITH cte mykey  AS       SELECT DISTINCT mykey FROM KV    SELECT  COMMA SEPARATED KEYS STRING AGG mykey      FROM cte  SELECT  COMMA SEPARATED KEYS AS keys      DECLARE  ExecuteExpression varchar MAX    DROP TABLE IF EXISTS  Pivoted   SET  ExecuteExpression   N  SELECT    INTO  Pivoted FROM       SELECT         mykey          myvalue          id person     FROM KV   AS t PIVOT      MAX t myvalue       FOR mykey IN  COMMA SEPARATED KEYS    AS pivot table      SET  ExecuteExpression   REPLACE  ExecuteExpression   COMMA SEPARATED KEYS    COMMA SEPARATED KEYS    EXEC  ExecuteExpression    SELECT   FROM  Pivoted    Msg 208  Level 16  State 0 Invalid object name   Pivoted   This is because  Pivoted is owned by the Dynamic SQL connection  So the last instruction  SELECT   FROM  Pivoted   fails   One way to not face this issue is to make sure all references to  Pivoted are made from inside the dynamic query itself    DECLARE  COMMA SEPARATED KEYS varchar MAX   DROP TABLE IF EXISTS KV  CREATE TABLE KV  id person int  mykey varchar 30   myvalue int   INSERT INTO KV VALUES  1   age   16    1   weight   63    1   height   175    2   age   26    2   weight   83    2   height   185   WITH cte mykey  AS       SELECT DISTINCT mykey FROM KV    SELECT  COMMA SEPARATED KEYS STRING AGG mykey      FROM cte  SELECT  COMMA SEPARATED KEYS AS keys    DECLARE  ExecuteExpression varchar MAX    DROP TABLE IF EXISTS  Pivoted   SET  ExecuteExpression   N  SELECT    INTO  Pivoted FROM       SELECT         mykey          myvalue          id person     FROM KV   AS t PIVOT      MAX t myvalue       FOR mykey IN  COMMA SEPARATED KEYS    AS pivot table  SELECT   FROM  Pivoted      SET  ExecuteExpression   REPLACE  ExecuteExpression   COMMA SEPARATED KEYS    COMMA SEPARATED KEYS    EXEC  ExecuteExpression

User · Answer

You can use OPENROWSET for this   Have a look   I ve also included the sp configure code to enable Ad Hoc Distributed Queries  in case it isn t already enabled   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

[sql] Insert results of a stored procedure into a temporary table

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to stored-procedures