How do we insert the results of the stored procedure in a table?

This question has two crinkle answers –

·         When the table is already created.

·         When the table is to be created run time.

Let us create a stored procedure which we will use for our example.

We can execute this stored procedure using the following script.

EXEC GetAllTables

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

1) Schema Known – Table Created Previously

If we know the schema of the stored procedure result set we can build a table previously and execute following code.




CREATE TABLE #TestTable ([TableName] VARCHAR(256), [TABLE_TYPE] CHAR(2));

INSERT INTO #TestTable

EXEC GetAllTables

-- Select Table

SELECT *

FROM #TestTable;

The drawback 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 result set of the stored procedure and we want to colonize the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',

'EXEC tempdb.dbo.GetAllTables')

-- Select Table

SELECT *

FROM #TestTableT;

The drawback of this code is that it bit complex but it generally works fine in the case of the column names are not known.

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

Hope this will give you some idea export stored procedure results to table.

Please share your thought on both the case.