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 Thursday, August 22, 2013 3:28 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 @ 8:36 AM
Points: 40,615, Visits: 37,080
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 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 #1487133
Posted Thursday, August 22, 2013 4:06 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
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?
Post #1487555
Posted Tuesday, November 4, 2014 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:52 AM
Points: 1, Visits: 1
Can we insert the results of above CTE into temp table?
Post #1630790
Posted Tuesday, November 4, 2014 6:09 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
You can, but you can't read the temp table after your initiating procedure regains control, the table has disappeared!
Post #1630925
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse