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)

About these ads

15 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

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

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

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

  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

  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?

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

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

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