content top
EXEC..INTO – Dreams Do Come True

EXEC..INTO – Dreams Do Come True

Many times in the past I wondered why we have 'Select Into' while 'Exec into' is of the absent.

Off course you can use   OPENROWSET for this, But you'll need to allow remote access to the server (which will have security ramifications) as well as enabling 'ad-hoc distributed queries'.

 Also, insert the connection string right and specify the full path to the stored procedure including the database name and the sp's owner. 

Another issue with OPENROWSET is that for order to insert into a TEMP table you'll need to SET FMTONLY OFF (in this case please notice that the SP will be ran twice).

To try and simplify this issue I created a SP that will return a table to read from. 

I called this SP 'spGetTableFromProcedure'. 

This SP can capture multiple result sets delivered by a single stored procedure execution, however all result sets must have the same Schema (column number, type and order).

This SP Get 5 params:

@Sp_Name: This param holds the name of the SP.

@DBName: The DB name you want to execute from. default is NULL which will give current DB.

@SchemaName: The SP's schema. Default is 'dbo'.

@Parameters: The parameters the SP gets. Default is NULL.

@NewTable: Will hold the name of the new table you want to EXEC INTO. Param default is '##TempFor'.
* Please notice that if you'll ask for a regular temp table (#Temp) it will be changed to '##Temp' (This is because the #Temp table created at a "lower level" than the block of executing code).

Please notice that SPs that use temp table can't be EXEC INTO.

Download the script.


2 תגובות על “EXEC..INTO – Dreams Do Come True”

  1. מאת Henrik Nordtorp:

    Hi Liran.

    Very cool script. I've only waited some 18 years for that to come by (started working with SQL Server 6.5).

    There is a slight issue though, when executing your sproc in one database and the target sproc in another. I guess it's the SCHEMA_NAME(schema_id) that runs locally an not in the target DB.

    But othervise a very nice and clean script. It's already embedded in my toolbox 😀 . Great job!

  2. Thank you for your kind words and your feedback. :-)
    I'm glad to see this solution was helpful for you.
    Regarding your input, i'll fix this issue soon.
    We can use join with Sys.Schemas instead.

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

eighteen + 16 =