Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, December 18, 2014 10:38 AM
Points: 2,052, Visits: 3,548
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!
Post #1486286
Posted Tuesday, August 20, 2013 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509

INSERT INTO #tempTable
EXEC dbo.someStoredProc


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

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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: Thursday, December 18, 2014 10:38 AM
Points: 2,052, Visits: 3,548
Awesome -- thanks!
Post #1486295
Posted Tuesday, August 20, 2013 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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: Tuesday, November 4, 2014 6:03 PM
Points: 26, Visits: 160
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 35,769, Visits: 32,438
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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: Tuesday, November 4, 2014 6:03 PM
Points: 26, Visits: 160
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