CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Matthew Baker
Matthew Baker

Reputation: 2729

SQL Server : inserting multi-table output from stored procedure into separate tables

I have a stored procedure that returns several tables (currently consumed by a web service). I now need to insert that output into a SQL Server database.

I can insert a single table if I know the existing definition via

INSERT INTO #temp
    EXEC ('myProcSQlhere')

But for this I need to know the definition of the output table in advance.

SELECT *
INTO #temp
FROM OPENQUERY(SERVERNAME, 'EXEC myProcSQlhere')

Which gets around knowing the table definition, but only allows entry into one table. So how do I insert data into multiple tables?

Upvotes: 4

Views: 1869

Answers (1)

Raul
Raul

Reputation: 3131

A stored procedure can only return one value. What your stored procedure does is performing multiple selects and so giving multiple result sets. This works for systems that can consume multiple result sets but you are stuck in SQL-Server.

Check if the stored proc doesn't already call independent stored procedures for each result set and if so, call those. Or use the definition of the stored procedure and call each query used within sequentially.

If you have no access to the system providing the stored procedure, you will need to implement a CLR stored procedure and fetch each result set to store the results into different tables.

Upvotes: 3

Related Questions