SQL server

Create a new table from stored procedure.

If you want to materialize the results of a store procedure, you could output the results to a file etc… I wanted to store the result set of a stored procedure into a table to do some further transformations and aggregating. I also did not have the table created and wanted to find the easiest possible way to insert the output of a stored procedure into a table, and create the table in the process. The select * into syntax worked for select queries, but didn’t work for my stored procedure. Doing a bit of trial and errors, I got my result. This is what I did:

SELECT * INTO staging_table FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,
     ‘exec database.dbo.stored_proc “parameter 1, parameter 2″‘)

Yes, the trick is in using openrowset. Now you need to have the Ad Hoc Distributed Queries feature enabled using sp_configure.  Here is what you can do:

sp_configure ‘Show Advanced Options’, 1
GO
RECONFIGURE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO

–Now do your query, and then reverse the changes by disabling ‘Ad Hoc Distributed Queries’

sp_configure ‘Ad Hoc Distributed Queries’, 0
GO
RECONFIGURE
GO
sp_configure ‘Show Advanced Options’, 0
GO
RECONFIGURE
GO

Please be careful and make sure that you do not disable the feature if it was previously enabled on your server.

Advertisements
Standard

Thinking about someting? 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s