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.
