Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Returning stored procedure results into a CTE or temp table? Expand / Collapse
Author
Message
Posted Tuesday, August 20, 2013 8:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 2,459, Visits: 4,376
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/
Post #1486286
Posted Tuesday, August 20, 2013 8:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 23, 2016 3:12 PM
Points: 3,737, Visits: 6,280

INSERT INTO #tempTable
EXEC dbo.someStoredProc


SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1486287
Posted Tuesday, August 20, 2013 9:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 45,297, Visits: 43,515
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

Post #1486294
Posted Tuesday, August 20, 2013 9:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 2,459, Visits: 4,376
Awesome -- thanks!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Post #1486295
Posted Tuesday, August 20, 2013 9:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, September 23, 2016 2:51 PM
Points: 15,981, Visits: 16,517
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1486312
Posted Tuesday, August 20, 2013 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 45,297, Visits: 43,515
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

Post #1486317
Posted Tuesday, August 20, 2013 9:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, September 23, 2016 2:51 PM
Points: 15,981, Visits: 16,517
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1486320
Posted Wednesday, August 21, 2013 6:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 2, 2016 3:44 AM
Points: 26, Visits: 171
Excellent suggestion. Also,

insert into #tab
exec (@MyDynamicCode)

But "insert into" will only insert into an existing table. What about creating a new table?
Post #1487014
Posted Wednesday, August 21, 2013 11:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 41,429, Visits: 38,763
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1487053
Posted Wednesday, August 21, 2013 11:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 2, 2016 3:44 AM
Points: 26, Visits: 171
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.
Post #1487061
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse