"SELECT * INTO table FROM" a stored procedure? Possible?

All replies

• 

  

  1

  Sign in to vote

  Sounds like you may need a table-valued function...
  
  Here's an example:
  
  

  USE Northwind

  go

  CREATE FUNCTION fx_Customers_ByCity

  ( @City nvarchar(15) )

  RETURNS table

  AS

  RETURN (

  SELECT CompanyName

  FROM Customers

  WHERE City =@City

  )

  go

  SELECT * FROM fx_Customers_ByCity('London')

  
  So in your case, you would define a function that accepts 3 parameters and returns table data and you call it like so:
  
  SELECT * INTO TableA FROM MyFunction('0001','P','P')
  

  ---

  --Brad

  • Proposed as answer by Brad_Schulz Monday, March 9, 2009 4:24 PM

  Monday, March 9, 2009 4:23 PM
• 

  

  0

  Sign in to vote

  Can the function call up a stored procedure and use conditions on the stored procedure result set? Or do I have to somehow convert what the stored procedure does to a function. I understand where you're going with this and it would work, but the store procedure statements are so complex it would take too long. If there is an easy way to do this it would be great.
  
  It would be faster to create a table with the corresponding columns to fit the result set from the stored procedure, execute the stored procedure, copy the result set to a CSV file, and then bulk insert that file into the table I just created.
  
  I just want to grab the data and put it into a new table in the most efficient way.

  ---

  If you keep doing the same thing, you'll always get the same thing...

  Monday, March 9, 2009 4:46 PM
• 

  

  0

  Sign in to vote

  I'm not sure what to tell you... I'm fairly new to this myself.
  
  As I understand it, a stored procedure will EXECUTE a batch but it doesn't actually RETURN a batch that you can then do something with (like put the batch of records INTO a new table).
  
  
  

  ---

  --Brad

  Monday, March 9, 2009 5:15 PM
• 

  

  0

  Sign in to vote

  the Insert statement support the execution statement (which includes the execution of stored procedure) as:
  
  INSERT table1 (col1)
  EXEC dbo.sp_some
  go
  
  For details, see section "H. Inserting data using the SELECT and EXECUTE options " at http://msdn.microsoft.com/en-us/library/ms174335.aspx
  
  

  Monday, March 9, 2009 5:21 PM
• 

  

  0

  Sign in to vote

  Brad_Schulz said:

  I'm not sure what to tell you... I'm fairly new to this myself.
  
  As I understand it, a stored procedure will EXECUTE a batch but it doesn't actually RETURN a batch that you can then do something with (like put the batch of records INTO a new table).
  
  
  

  ---

  --Brad

  
  
  No problem, thanks for the idea with a function as that will work and I will keep that in mind moving forward. Any other feedback is greatly appreciated!

  ---

  If you keep doing the same thing, you'll always get the same thing...

  Monday, March 9, 2009 5:26 PM
• 

  

  1

  Sign in to vote

  There'a also an OPENQUERY() function, but that's designed to retrieve a set of records from some other linked server:
  
  SELECT * INTO TableA FROM OPENQUERY(SomeLinkedServer,'EXEC sp_List ''0001'',''P'',''P''')
  
  I don't know if you can manipulate this into just executing on the same server you're already on or not.
  

  ---

  --Brad

  Monday, March 9, 2009 5:31 PM
• 

  

  0

  Sign in to vote

  Bill Fan said:

  the Insert statement support the execution statement (which includes the execution of stored procedure) as:
  
  INSERT table1 (col1)
  EXEC dbo.sp_some
  go
  
  For details, see section "H. Inserting data using the SELECT and EXECUTE options " at http://msdn.microsoft.com/en-us/library/ms174335.aspx
  
  

  
  
  I totally agree with you and fully understand how this works, but I'm trying to avoid going this route as I do not want to create a table to insert into from the stored procedure; hence the reason I want to use the SELECT * INTO table FROM stored_procedure approach but was wondering if this is possible.
  
  The result set from the store procedure in question has 50-some columns. I'm just trying to avoid having to create the table unless I have to and learn some new tricks at the same time.
  
  I can always create a table and insert into it from the stored procedure but how nice would it be if I could just do the whole thing in one process only.

  ---

  If you keep doing the same thing, you'll always get the same thing...

  Monday, March 9, 2009 5:32 PM
• 

  

  0

  Sign in to vote

  From a PROC you can call the function but not viceversa. So create a temp proc like this
  
  Create Proc #tempProc.
  
  With the results coming from your function, create a temp table like
  Declare @temptable table
  
  then insert the values coming from function to this temp table. If you want to keep this table as permanent, then create a permanent table.
  
  HTH.

  Monday, March 9, 2009 5:35 PM
• 

  

  3

  Sign in to vote

  A stored procedure allows a lot more flexiblity while maintaing parameterization, while a TVF (Table Valued Function) can operate as a paramertized view, if it is inline.  Here is an article that well help you in deciding between a function and a stored procedure.  You should be careful with the use of TVF function though.  The optimizer is only able to maintain statistics on an Inline TVF... any other type of fuction will require the optmizer to guess at the number of rows affected, which can severly degrade performance.
  
  Table Valued Functions: http://msdn.microsoft.com/en-us/library/ms191165.aspx
  *Inline TVF Link: http://msdn.microsoft.com/en-us/library/ms189294.aspx
  Rewrittings SP as Functions: http://msdn.microsoft.com/en-us/library/ms187650.aspx
  
  As for the answer to the original question.
  
  The syntax to insert a stored procedures results into a table is as such.
  
  

  INSERT INTO MyTable

  (

  Col1,

  Col2

  )

  EXEC [dbo].[MyStoredProcName]

  • Edited by Adam Haines Monday, March 9, 2009 7:20 PM Added more content
  • Marked as answer by Naomi N Wednesday, February 29, 2012 9:57 PM

  Monday, March 9, 2009 7:11 PM
• 

  

  0

  Sign in to vote

  The problem is not that he wants to INSERT INTO a table... he wants to CREATE a table via the INTO clause of a SELECT.
  
  And that doesn't seem possible with a proc.
  
  Either way, it seems that he's going to have to define the 50+ fields of the table somewhere, which is what he's trying to avoid.  He'll either have to CREATE TABLE with the field definitions and do an INSERT INTO as you suggested, or he'll have to define the table definition in the UDF. 
  
  Unless there's some other way.
  
  

  ---

  --Brad

  Monday, March 9, 2009 7:17 PM
• 

  

  0

  Sign in to vote

  Here's another idea...
  
  Can you create a 4th parameter for your sp_List procedure (and by the way, you really shouldn't name your stored procedures beginning with "sp_") and that parameter is an optional name of a table to create when your procedure does its SELECT.
  
  If you pass NULL, it just does an ordinary SELECT.  If you pass a string of some kind, then it does the SELECT with an INTO clause into a table defined by that string.  You'd have to construct a dynamic SQL command in your proc to do this, though.
  

  ---

  --Brad

  • Proposed as answer by Brad_Schulz Monday, March 9, 2009 7:25 PM

  Monday, March 9, 2009 7:24 PM
• 

  

  1

  Sign in to vote

  Brad_Schulz said:

  The problem is not that he wants to INSERT INTO a table... he wants to CREATE a table via the INTO clause of a SELECT.
  
  And that doesn't seem possible with a proc.
  
  Either way, it seems that he's going to have to define the 50+ fields of the table somewhere, which is what he's trying to avoid.  He'll either have to CREATE TABLE with the field definitions and do an INSERT INTO as you suggested, or he'll have to define the table definition in the UDF. 
  
  Unless there's some other way.
  
  

  ---

  --Brad

  
  
  Correct, there is no magic button that will automatically create the column list for him.  The best method is just list the columns. He will only need to do this one time, after which is just becomes simple maintenance. To do anything else would cause either performance problems or security issues (dynamic sql). 
  
  There is one trick that can be done and that is to right-click the table/view --> script table as --> select to --> new query window.  This will generate a select statement with all the columns listed.  He can copy the columns and paste them in his create table statement.
  
  Edit: I just saw your post and I advise anyone who use select into to be very very careful. I actually dont like anyone using this in my environment.  While the feature is good, what is untold by most is how this method signficantly drags performance.  The reason is the DB engine has to lock tempdb while it tries to build all the data and query meta data looking for definitions.  This can cause severly locking for other temp object creation and can even slow down other processes. If you feel obligated to use this method the perferred way is to use an invalid where clause, followed by an insert into statement, as this minimizes locking.  Trust me on this :).
  
  Here is a sample:
  

  SELECT *

  INTO #temp

  FROM MyTable

  WHERE 1 = 2

  INSERT INTO #temp

  SELECT *

  FROM MyTable

  • Edited by Adam Haines Monday, March 9, 2009 7:32 PM Added more content

  Monday, March 9, 2009 7:26 PM
• 

  

  0

  Sign in to vote

   Can't you store the output parameters from the SP into a variable using something like this
  
  

  sp_sproc_columns @procedure_name = 'yourprocname'
  

  Monday, March 9, 2009 7:45 PM
• 

  

  0

  Sign in to vote

  Agreed...  The SELECT INTO with a WHERE 1=2 is an excellent idea.
  

  ---

  --Brad

  Monday, March 9, 2009 7:45 PM
• 

  

  2

  Sign in to vote

  Hi, It's definitely not a part of best practices  , but it works:
  

  select a.* into #t
  
  from openrowset('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',

  'SET NOCOUNT ON;SET FMTONLY OFF ;
  
  exec msdb..sp_help_job') AS a;
  
  select * from #t;

  • Marked as answer by Naomi N Wednesday, February 29, 2012 9:59 PM

  Monday, March 9, 2009 7:46 PM
• 

  

  0

  Sign in to vote

  Here's what I mean as an example... the first 3 parameters don't do anything in this example (except @parm1 is used in the WHERE clause), but you get the idea...
  
  

  create proc pGetStuff (

  @parm1 varchar(4)

  ,@parm2 varchar(1)

  ,@parm3 varchar(1)

  ,@intotable varchar(100)=null

  )

  as

  begin

  declare @sql nvarchar(max)

  if @intotable is not null

  begin

  if object_id(@intotable) is not null

  begin

  set @sql='drop table '+@intotable

  exec sp_executesql @sql

  end

  end

  set @sql='select *'+coalesce(' into '+@intotable,'')+

  ' from SomeTable where SomeField='+@parm1

  --print @sql

  exec sp_executesql @sql

  end

  
  Now you can do this:
  
  

  exec pGetStuff '0001','P','P'             --Just does the SELECT

  exec pGetStuff '0001','P','P','NewTable'  --SELECTs and creates table called NewTable

  
  Again, this doesn't institute Adam's suggestion of using the WHERE 1=2 approach to minimize locking.  But hopefully it will give you an idea.
  

  ---

  --Brad

  • Proposed as answer by Brad_Schulz Monday, March 9, 2009 7:56 PM

  Monday, March 9, 2009 7:56 PM
• 

  

  0

  Sign in to vote

   I think Denis solution works in this case.
  Here is similar link
  
  
  http://wiki.lessthandot.com/index.php/Store_The_Output_Of_A_Stored_Procedure_In_A_Table_Without_Creating_A_Table
  
  
  

  • Marked as answer by Jinchun ChenMicrosoft employee Friday, March 20, 2009 7:00 AM
  • Edited by Jinchun ChenMicrosoft employee Friday, March 20, 2009 7:06 AM The original is shown with scrolling bar

  Monday, March 9, 2009 8:30 PM
• 

  

  0

  Sign in to vote

  You're probably right...
  
  Denis' solution is BY FAR the easiest solution... thanks for posting the link.
  

  ---

  --Brad

  Monday, March 9, 2009 8:47 PM
• 

  

  0

  Sign in to vote

  Brad_Schulz said:

  There'a also an OPENQUERY() function, .....
  I don't know if you can manipulate this into just executing on the same server you're already on or not.
  

  ---

  
  Yes Brad, you can execute OPENQUERY on the same server instance (not just on a linked server) and achieve the objective of creating a table with SELECT INTO from stored procedure execution.
  
  Here is a SQL Server 2008 OPENQUERY demo on the same server instance:
  
  

  /*  Data access must be configured for the sql server instance

  exec sp_serveroption @server = 'DELLSTAR\SQL2008'

  ,@optname = 'DATA ACCESS'

  ,@optvalue = 'TRUE'

  */

  -- SQL select into temporary table create from stored procedure execution

  SELECT *

  INTO   #BillOfMaterials

  FROM   OPENQUERY([DELLSTAR\SQL2008],

  'EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]  800,''2004-02-01''')

  GO

  SELECT   TOP ( 5 ) *

  FROM     #BillOfMaterials

  ORDER BY NEWID()

  GO

  /* Partial results

  ProductAssemblyID  ComponentID  ComponentDesc

  835                  803          ML Fork

  800                  835          ML Road Frame-W - Yellow, 44

  806                  323          Crown Race

  800                  994          LL Bottom Bracket

  800                  819          ML Road Front Wheel

  */

  SELECT FROM Stored Procedure article:

  http://www.sqlusa.com/bestpractices2005/selectfromsproc/

  ---

  Kalman Toth
  New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

  
  

  
  

  • Marked as answer by Naomi N Wednesday, February 29, 2012 9:59 PM
  • Edited by Kalman Toth Friday, November 10, 2017 10:06 AM

  Monday, March 9, 2009 9:26 PM
• 

  

  0

  Sign in to vote

  Thanks, Kalman... that's cool.
  
  Simple question... Is there a function or SP that will return the current server name (DELLSTAR\SQL2008 in your example)?
  
  

  ---

  --Brad

  Monday, March 9, 2009 9:39 PM
• 

  

  0

  Sign in to vote

   

  select @@SERVERNAME

  Monday, March 9, 2009 9:46 PM
• 

  

  0

  Sign in to vote

  Yum64147 said:

   

  select @@SERVERNAME

  
  
  
  Ah-hah!  So it was NEITHER a function or an SP... it's a variable!
  
  Thanks.
  
  Now how can that be passed to OpenQuery()?
  

  ---

  --Brad

  Monday, March 9, 2009 10:14 PM
• 

  

  0

  Sign in to vote

  Brad_Schulz said:

  Thanks, Kalman... that's cool.
  
  Simple question... Is there a function or SP that will return the current server name (DELLSTAR\SQL2008 in your example)?
  
  

  ---

  --Brad

  
  
   Yes. A system variable:
  
  

  select @@SERVERNAME

  
  Or SERVERPROPERTY funcion:
  
  

  SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

  ---

  Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI:  www.SQLUSA.com   Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
   

  • Edited by Kalman Toth Friday, November 10, 2017 10:07 AM

  Monday, March 9, 2009 10:47 PM
• 

  

  0

  Sign in to vote

  Thanks, Kalman...
  
  Now, once we have that, can it be passed to OpenQuery()?
  
  In other words, can the first parameter of OpenQuery() not be hard-coded in the SP?
  

  ---

  --Brad

  Monday, March 9, 2009 11:03 PM
• 

  

  0

  Sign in to vote

  Brad,
  
  I am afraid (or joyous) we have to go dynamic SQL to do that. The linked_server parm is hard-wired in OPENQUERY. Note the septuple single quotes, a trademark of heavy-duty dynamic query.
  
  Here is the dynamic SQL SELECT INTO table create from sproc execution demo:
  
  

  /*  Data access must be configured

  exec sp_serveroption @server = 'DELLSTAR\SQL2008'

  ,@optname = 'DATA ACCESS'

  ,@optvalue = 'TRUE'

  */

  -- SQL select into table create from stored procedure execution

  -- T-SQL dynamic SQL

  DECLARE @SQL nvarchar(max)

  SET @SQL =

  'SELECT *

  INTO   BillOfMaterials

  FROM   OPENQUERY(' + QUOTENAME(CONVERT(sysname, SERVERPROPERTY('servername')))+ ',

  ''EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]  800,

  ''''2004-02-01'''''')'

  PRINT @SQL  -- test & debug

  EXEC sp_executeSQL @SQL

  SELECT   TOP ( 5 ) *

  FROM    BillOfMaterials

  ORDER BY NEWID()

  GO

  /* Partial results

  ProductAssemblyID   ComponentID ComponentDesc

  324                 486         Metal Sheet 5

  800                 950         ML Crankset

  950                 332         Freewheel

  950                 318         ML Crankarm

  398                 487         Metal Sheet 6

  */

  -- Cleanup

  DROP TABLE BillOfMaterials

  GO

  /*  select @@SERVERNAME

  SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

  */

  ---

  Kalman Toth
  New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

  
  
  

  • Edited by Kalman Toth Friday, November 10, 2017 10:07 AM

  Tuesday, March 10, 2009 2:21 AM
• 

  

  0

  Sign in to vote

  I am afraid (or joyous) we have to go dynamic SQL to do that. The linked_server parm is hard-wired in OPENQUERY.
  
  Ahhh... that's what I figured... 
  
  Note the septuple single quotes, a trademark of heavy-duty dynamic query.
  
  YEESH!
  
  

  ---

  --Brad

  Tuesday, March 10, 2009 2:30 AM
• 

  

  0

  Sign in to vote

  Hey guys or gals, thanks for all the input. I didn't realize this was gonna turn into a monster, haha. I thought it was going to be a quick answer telling me it wasn't really possible.
  
  I'll review this thread in detail tomorrow.
  
  Thanks very much! I always enjoy making all your great minds tick! I appreciate the help with this. I learned a lot!

  ---

  If you keep doing the same thing, you'll always get the same thing...

  Tuesday, March 10, 2009 5:37 AM
• 

  

  1

  Sign in to vote

   

  INSERT <TableName> EXEC <Command>

  
  
  

  insert tablea EXECUTE sp_List '0001','P','P'

  
  

  ps,sp can not nested.

  • Proposed as answer by Mike Albert Tuesday, July 14, 2009 8:51 PM

  Tuesday, March 10, 2009 7:28 AM
• 

  

  0

  Sign in to vote

  I know that i am being quite late in this discussion, but can't one just have the stored procedure output a table valued parameter (ofcourse this needs you to be able to modify the stored procedure). This table valued parameter can then be used in the calling script to insert data into a table.

  Wednesday, May 13, 2009 8:24 PM
• 

  

  0

  Sign in to vote

  All of the ways to use the result set from a stored procedure have their issues.  Readers who would like to see better support for this are encouraged to vote on this Connect issue:
  
  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=294571

  Wednesday, May 13, 2009 8:36 PM
• 

  

  0

  Sign in to vote

  Hi, It's definitely not a part of best practices  , but it works:
  

  select

   

   

   

   

  a.* into #t
  
  from openrowset('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',

   

   

  'SET NOCOUNT ON;SET FMTONLY OFF ;
  
  exec msdb..sp_help_job') AS a;
  
  select * from #t;

  Man, they skipped right over your correct answer.
  

  ---

  --Jeff Moden

  Monday, May 25, 2009 2:34 AM
• 

  

  0

  Sign in to vote

  Didn't Brad mention OPENQUERY a couple of hours earlier?  ;c)

  
  

  Paul

  Monday, May 25, 2009 5:19 AM
• 

  

  0

  Sign in to vote

  Yes, he most certainly did... I missed it.  Both solutions work just fine.

  ---

  --Jeff Moden

  Tuesday, May 26, 2009 2:15 AM
• 

  

  0

  Sign in to vote

  Stone Z!  Way to sneak in here and provide the best solution to this problem in my book.  I implemented to and it worked without a hitch...
  
  

  ALTER PROCEDURE dbo.GetChildProducts
  	(
  	@baseSectionID int,
  	@recursive bit
  	)
  AS
  	SET NOCOUNT ON
  	CREATE TABLE [#GetChildProducts_Temp]
  	(
  		topSectionID int,
  		sectionID int,
  		sectionName varchar(255)
  	)
  	INSERT [#GetChildProducts_Temp] EXEC dbo.GetAllChildSections @baseSectionID, @recursive
  	--SELECT * FROM #GetChildProducts_Temp
  	SELECT DISTINCT products.pID
  	FROM     products INNER JOIN
  				   [#GetChildProducts_Temp] AS q1 ON products.pSection = q1.sectionID
  	UNION
  	SELECT DISTINCT multisections.pID
  	FROM     multisections INNER JOIN
  				   [#GetChildProducts_Temp] AS q1 ON multisections.pSection = q1.sectionID
  	DROP TABLE #GetChildProducts_Temp
  	RETURN
  

  • Edited by Mike Albert Tuesday, July 14, 2009 8:56 PM

  Tuesday, July 14, 2009 8:51 PM
• 

  

  0

  Sign in to vote

  Hello,

  With your query above, how can I pass a value for a parameter in my stored proc?

  Thank you,

  Alimda

  Friday, March 7, 2014 8:48 PM
• 

  

  0

  Sign in to vote

  When I use your statement, I get "An INSERT EXEC statement cannot be nested."

  This is my code:

  CREATE TABLE #Table (Col1, Col2, Col3)
  

  INSERT INTO #Table
  

  EXEC [dbo].[usp_procedure]
          @1st Parameter = N'variable',
          @2nd Parameter = N'variable'
  
  GO

  Wednesday, April 25, 2018 4:51 PM
• 

  

  0

  Sign in to vote

  Yah! once again someone linked to an article that is no longer accessible.  Good for you, we'll be sending out our dumbass award presently.  When will Microsoft stop allowing this??

  Friday, August 28, 2020 10:56 AM
• 

  

  0

  Sign in to vote

  Yah! once again someone linked to an article that is no longer accessible.  Good for you, we'll be sending out our dumbass award presently.  When will Microsoft stop allowing this??

  Answer: on Tuesday, when this forum will be readonly!

  The thread you jumped into is over ten years old, so it is not really unexpected if links in it no longer works.
  If you were particularly interested in the topic in the subject line, there is an article on my web site where discuss variations of this problem:
  http://www.sommarskog.se/share_data.html

  (That link, by the way, has been valid for about 18 years, although the contents have changed over the years.)

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Proposed as answer by Naomi N Friday, August 28, 2020 12:44 PM

  Friday, August 28, 2020 12:37 PM
• 

  

  0

  Sign in to vote

  Yes, I just checked it - unfortunately the site went belly up a few years ago - only blogs are still accessible :( I blogged on that site and also on BeyondRelational.com and both sites were thriving 10+ years ago but both were closed for a few years already...

  I'll leave my answer as is - it was valid at the time I posted it and hopefully helped at that time.

    BTW, I think I should thank Erland for the "belly up" expression - I believe I learnt it from you originally, but I may be wrong...

  ---

  Looking for new opportunities

  For every expert, there is an equal and opposite expert. - Becker's Law

  
  My blog
  
  My TechNet articles

  
  
  

  • Edited by Naomi N Friday, August 28, 2020 1:13 PM

  Friday, August 28, 2020 12:56 PM