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 GetAllTablesNow 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', 1GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOHope this will give you some idea export stored procedure results to table.
Please share your thought on both the case.
Please share your thought on both the case.
