Article Information
This SQL Tutorial contains and tries to cover following subjects:
- Explanation of Inserting a stored procedure result set into table in sql.
- Example to Inserting a stored procedure result set into table in sql
Articles tries to provide answer to following questions
- How to insert a result of stored procedure into table
- How to insert a stored procedure result table to another table in sql?
- How to use sql insert to add stored procedure result to table in sql?
Article covers followings indirectly:
- SQL insert command
- SQL insert EXEC
- Stored Procedures
Articles pre-requisites following information:
- General knowledge of SQL Server Management Studio
- General knowledge of INSERT command
Inserting a stored procedure result set into table
SQL INSERT had been covered in earlier article to demonstrate how to insert a row, a result of SELECT in INSERT command to insert into a table the result. SQL INSERT also supports inserting Stored Procedure results. When a stored procedure is executed, it provides as a result set, columns and rows. If this is the case that we want to insert result rows of stored procedure into another table in our INSERT command, SQL INSERT works with EXEC SPs.
General rule of INSERT applies to SQL INSERT EXEC. The result table or rows need to match to the table INSERT is going for. Column numbers and data types of columns need to match. Otherwise insert can fail.
Lets recall basic sql INSERT syntax again:
INSERT (column1, column2) VALUES (value1, values2)
To insert data from a Stored Procedure, syntax will be as follows:
INSERT (column1, column2) EXEC stored procedure name
Lets see in a sql insert example which gets data from a Stored Procedure as practice
As above sql insert example indicates, new row has been inserted into the table. We provided two column name, and values come from Stored Procedure which contains a SELECT statement. The SELECT statement could be a select from a table to insert entire table in sql insert. Moreover, we could add WHERE or another filters to get data from another table and insert it to another.