Returning stored procedure results into a CTE or temp table?

Question

Post reply

Returning stored procedure results into a CTE or temp table?

Ray K

SSC-Dedicated

Points: 31163
More actions
August 20, 2013 at 8:50 am

#281364

Hi all . . .
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
Thanks in advance!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

INSERT INTO #tempTable

EXEC dbo.someStoredProc

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The INSERT ... EXEC can be used to insert into a table variable or temp table. A CTE however, despite its name, is not a table and hence can not have anything inserted into it. Think of a CTE as a temporary view, so a select statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

Awesome -- thanks!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.

Acutally you can insert into a cte, just like you can delete from them.

create table cteInsert

(

SomeValue varchar(20)

);

with myCTE as

(

select SomeValue

from cteInsert

)

insert myCTE

select 'Inserted'

select * from cteInsert;

with myDeleteCte as

(

select SomeValue

from cteInsert

)

delete myDeleteCte

select * from cteInsert;

drop table cteInsert

In reference to the thread however it doesn't make much sense. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Sean Lange (8/20/2013)
GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.
Acutally you can insert into a cte, just like you can delete from them.

No, actually you can't. What you can do is insert into a table (permanent, temporary or variable) through a CTE, just as you can with a view (if it is updateable).

You're not inserting into the CTE because a CTE has no persistent storage, it's just a select statement. The insert is into the base tables that the CTE is defined upon.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

GilaMonster (8/20/2013)
Sean Lange (8/20/2013)
GilaMonster (8/20/2013)
A CTE however, despite its name, is not a table and hence can not have anything inserted into it.
Acutally you can insert into a cte, just like you can delete from them.
No, actually you can't. What you can do is insert into a table (permanent, temporary or variable) through a CTE, just as you can with a view (if it is updateable).
You're not inserting into the CTE because a CTE has no persistent storage, it's just a select statement. The insert is into the base tables that the CTE is defined upon.

True enough. I should have said that you can use them as the source for those operations but the data will really reside in the base table. Thanks for the correction.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

cmerrell Mr or Mrs. 500 Points: 522 More actions · Answer 7

Excellent suggestion. Also,

insert into #tab

exec (@MyDynamicCode)

But "insert into" will only insert into an existing table. What about creating a new table?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

cmerrell (8/21/2013)
Excellent suggestion. Also,
insert into #tab
exec (@MyDynamicCode)
But "insert into" will only insert into an existing table. What about creating a new table?

Use SELECT/INTO with OPENROWSET to call the proc. If you want to pass parameters to the proc, it's all going to have to be dynamic SQL because OPENROWSET can't take parameters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cmerrell Mr or Mrs. 500 Points: 522 More actions · Answer 9

Thanks 🙂 , but I'm trying to run some dynamic sql which I have built into a memory variable, rather than calling a stored procedure. Something like:

select *

into #tab

from exec (@MyDynamicCode)

which I know is wrong. I may not have rights to use openrowset, and anyway, using OLE DB for dealing with SQL server tables within SQL Server seems somehow inappropriate.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

cmerrell (8/21/2013)
But "insert into" will only insert into an existing table. What about creating a new table?

CREATE TABLE ...

INSERT INTO

EXEC ...

Caveat is that you have to know the structure of the result set, but to be honest if you have a procedure that's non-deterministic in terms of the result set it's returning, there's probably something deeper that needs fixing/changing

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cmerrell Mr or Mrs. 500 Points: 522 More actions · Answer 11

The output table is the result of a dynamic pivot, so the columns created depends on the values in the data. Yes, its non-deterministic, but fairly standard practise, is it not?

sarojini.vempati SSC Journeyman Points: 87 More actions · Answer 12

Can we insert the results of above CTE into temp table?

cmerrell Mr or Mrs. 500 Points: 522 More actions · Answer 13

You can, but you can't read the temp table after your initiating procedure regains control, the table has disappeared!