T-SQL 101: 138 Inserting Rows Returned by a SQL Server Stored Procedure

Another way you can get rows to insert into a table is by executing a stored procedure or, in fact, even by executing dynamic SQL. Either way, these both use an EXEC statement.

In the example shown, I’m doing an INSERT into the dbo.CinemaGroups table. I’ve declared the two columns that are being inserted. The stored procedure returns the rows that I want to insert.

This is again fairly easy to troubleshoot because the EXEC clause can usually be executed by itself, and you can see the rows coming back.

Matching up columns

One of the challenges that we’ve had with this is that whoever wrote the stored procedure might have called things different names or might have had slightly different data types than what we want. They might also change the procedure to return extra columns.

Since SQL Server 2012, you can also add a WITH ROW SETS clause. It’s a special clause you can add to an EXEC statement. It allows us to rename or redefine the columns that come back from a stored procedure. That’s a relatively advanced concept that we’ll discuss in the future.

Most of the time, this isn’t something you’d need to do. What you need to do, though, is make sure that the column list for your INSERT matches the list of columns that are coming back from the stored procedure.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-04-01