andrewlocatelliwoodcock

Thoughts on Software

Inserting the results of a stored procedure into a table

leave a comment »


There’s an extremely useful feature in T-SQL that let’s you insert a rowset from a stored procedure or function directly into a table or (from SQL Server 2005 onwards) a table variable.

Where this comes in really handy is that it allows you to manipulate the output of a stored procedure outside the context of that stored procedure, basically allowing better code reuse.

This feature is not undocumented and is hardly a state secret but whilst I can always remember that it exists, I can’t always remember how to use it, hence this post …

Examples:

INSERT #myTemporaryTable EXEC myStoredProcedure

INSERT myTable EXEC myStoredProcedure

INSERT @myTableVariable EXEC myStoredProcedure

There is one caveat however: the structure of the table must match the output of the stored procedure, so if the stored procedure is updated, this can break unrelated code …

Written by andrewlocatelliwoodcock

May 6, 2011 at 14:44

Posted in SQL Server, T-SQL

Tagged with , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: