SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

Here is a very common question which I keep on receiving on my facebook page as well on twitter.

“How do I insert the results of the stored procedure in my table?”

This question has two fold answers – 1) When the table is already created and 2) When the table is to be created run time. In this blog post we will explore both the scenarios together.

However, first let us create a stored procedure which we will use for our example.

CREATE PROCEDURE GetDBNames
AS
SELECT
name, database_id
FROM sys.databases
GO

We can execute this stored procedure using the following script.

EXEC GetDBNames

Now let us see two different scenarios where we will insert the data of the stored procedure directly into the table.

1) Schema Known – Table Created Beforehand

If we know the schema of the stored procedure resultset we can build a table beforehand and execute following code.

CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
-- Select Table
SELECT *
FROM #TestTable;

The disadvantage of this code is that if due to any reason the stored procedure returns more or less columns it will throw an error.

2) Unknown Schema – Table Created at Runtime

There are cases when we do know the resultset of the stored procedure and we want to populate the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;

The disadvantage of this code is that it bit complicated but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

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

I will be interested to know which of the above method do you use in your projects? and why?

Click to Download Scripts

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

31 thoughts on “SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

  1. creating a new table from a query is very easy:
    SELECT name, database_id into NewTable
    FROM sys.databases;
    go
    select * from NewTable;
    go
    drop table newTable;
    go

    Like

      • OK I’ve given the simplest example but it works just as well for complicated queries with joins, where, group by, having etc.

        In fact we use this method every night to remake tables with 70 000 lines that we use for statistics which are based on a view using 5 tables and reasonably complicated conditions and totals: the users can query the table in less than five seconds, compared to about two minutes for the view.

        Like

        • Your proposed solution has nothing to do with the question. The question was inserting results from a stored procedure into a table. Nowhere did you address that.

          Like

    • if SP return more than one result set then, the result will be the result of first select statement.

      try below code:
      CREATE PROCEDURE GetDbNames
      AS
      SELECT [name] from sys.databases
      SELECT [name],[database_id] from sys.databases
      GO
      EXEC GetDbNames

      SELECT * INTO #Test1 FROM OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames’)
      SELECT * FROM #Test1
      DROP TABLE #Test1

      Now, the temp table have only the result of “SELECT [name] from sys.databases”

      Like

  2. If SP return more than one result set then,

    the result in temp table will be the result of first select statement in sp.

    try below query:

    CREATE PROCEDURE GetDbNames
    AS
    SELECT [name] from sys.databases
    SELECT [name],[database_id] from sys.databases
    GO
    EXEC GetDbNames

    SELECT * INTO #Test1 FROM OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames’)
    SELECT * FROM #Test1
    DROP TABLE #Test1

    Like

  3. As we are not a database admins in our project. Whenever we created the SP we are the admins (during development) and SP executing successfully and data also inserted , but now the project in the live and we can’t use admin account for SP execution. Now DBA team is requesting us to change the SP to insert the EXCEL data to database table.

    I have given these queries to DBA team to execute:

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

    but they are asking why to execute these, why can’t you change the SP.

    Please help me on this?

    Like

    • It can be done like this :
      OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames @job_name = N”jobname”’)

      Like

      • Hi Karthik,

        Here you have entered hardcoded value in the parameter but if I want to pass dyanamic parameter then. Because I am trying to pass dyanamic paramenter using variable but it is giving me error “Incorrect Syntax near ‘+’. Expecting ‘)’.”

        Please help

        Thanks,
        Mangesh

        Like

  4. My stored procedure need to create temporary tables first. I used the second method to avoid dealing with a lot un-useful.columns. The select into statement came back error saying the temporary table name as “invalid object name”.

    Like

  5. I believe this method is brilliant for everything except when the stored procedure is using temporary tables. I am trying to use the results of my dynamic pivot stored procedure with the above code and get the following error.

    The metadata could not be determined because statement ‘INSERT INTO #Foo(Col1, Col2, ColNPlusOne)
    SELECT Col1 ‘ in procedure ‘sp_DynamicSurveyPivotData’ uses a temp table.

    Think this is just a limitation of Sql Server 2012.

    Like

  6. IMHO, you should never use select/into with tempdb as this causes concurrency issues. Always use create table and then insert your results. Your locking of the system tables in tempdb will be much shorter than a select/into which has to wait for entire query to process before it can release the locks it holds.

    Like

  7. The INSERT will also take column names, so if new Docolumns are added, it won’t break the code. E.g.,

    INSERT INTO #MyTable
    (MyColumn1, MyColumn2)
    Exec MyProc

    But this can cause performance issues (in my experience).

    To answer your question about why this is needed, I like to use inserting into tablea this way for code re-usability.

    Like

  8. SELECT * INTO #TestTableT FROM OPENROWSET(‘SQLNCLI’, ‘Server=NEHA-PC;Trusted_Connection=yes;’,
    ‘exec OCFSERVER.dbo.Performance ”2014/06/01”,”2014/06/30”,868440’)

    this is not working
    error:
    Msg 208, Level 16, State 1, Procedure Performance, Line 16
    Invalid object name ‘#TempPerformance’.

    #TempPerformance is my temp table in OCFSERVER.dbo.Performance..
    please tell me how to work..

    Thank if any one reply

    Like

  9. I have the same question as UnKnMsTr… I have tried the following:

    SELECT * INTO #DistinctRels FROM OPENROWSET(‘SQLNCLI’, ‘Server=ATO-TAS;Trusted_Connection=yes;’,
    ‘EXEC sp_get_all_distinct_relationship_information Govern’)

    Where “Govern” is the parameter I need to pass to my SP. I get the following error:
    Could not find stored procedure ‘sp_get_all_distinct_relationship_information’.

    Yet, when I run the SP by itself:
    sp_get_all_distinct_relationship_information ‘Govern’, it works fine

    Like

  10. create table #DB_Size_With_All_Tables
    (
    name varchar(max),
    rows int,
    reserved varchar(max),
    data varchar(max),
    index_size varchar(max),
    unused varchar(max)
    )
    declare @tbl_nm varchar(max)
    declare @script varchar(max)
    declare curr cursor local for select name from sys.tables
    OPEN curr

    FETCH NEXT FROM curr INTO @tbl_nm

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SELECT @script = ‘insert into #DB_Size_With_All_Tables exec sp_spaceused ‘+@tbl_nm
    EXECUTE (@script)
    FETCH NEXT FROM curr INTO @tbl_nm
    END
    close curr
    deallocate curr
    select * from #DB_Size_With_All_Tables
    drop table #DB_Size_With_All_Tables

    Like

  11. I want to put this dynamic pivot query in Table-valued Function.
    can you sir please help me.

    Declare @cols NVARCHAR(MAX);
    Declare @query AS NVARCHAR(MAX);

    Set @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(ProjectProperties.PropertyId)
    FROM ProjectProperties
    FOR XML PATH(”) ,TYPE
    ).value(‘.’, ‘NVARCHAR(MAX)’),
    1, 1, ”)

    set @query = ‘SELECT ProjectId, ‘ + @cols + ‘ from
    (
    select ProjectId, PropertyId, PropertyValue
    from ProjectProperties
    ) x
    pivot
    (
    max(PropertyValue)
    for PropertyId in (‘ + @cols + ‘)
    ) p ‘

    execute(@query)

    Like

  12. Actually i have data in row so i have converted it in columns by this type of query,
    Now i want add it in table valued function so i can join this table in my procedure query and get as my desire output with procedure query. but column is not fix and this is dynamic query due to that i can not add this is in table valued function, is it possible to add this in table valued function?

    Like

    • if bellow query for create function is possible then also fine, i’ll add my pivot query in procedure and call this procure from function.

      CREATE FUNCTION Test()
      RETURNS TABLE AS
      RETURN
      (
      exec sp_test
      )

      Like

  13. Hi Jignesh i would like to do the same, i have the same piece of code that is generating a pivot table dynamically and would like to be able to have it as a view to link it to my main table. Did you find a solution to this?
    Many thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s