| Author |
Topic |
|
ikhuram
Starting Member
28 Posts |
 Posted - 2004-06-01 : 10:46:23
|
| How to insert the results of a stored procedure into a temporary table or open a cursor on it? |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-01 : 11:12:17
|
| INSERT INTO #temptable EXEC sp_storedprocnameRaymond |
 |
|
|
ikhuram
Starting Member
28 Posts |
Posted - 2004-06-01 : 11:15:23
|
| Its giving error:Server: Msg 208, Level 16, State 1, Line 1Invalid object name '##temptable'. |
|
|
|
ikhuram
Starting Member
28 Posts |
Posted - 2004-06-01 : 11:15:42
|
| Its giving error:Server: Msg 208, Level 16, State 1, Line 1Invalid object name '#temptable'. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-01 : 11:17:05
|
| Have you declare the temp table first?Raymond |
|
|
|
ikhuram
Starting Member
28 Posts |
Posted - 2004-06-01 : 11:18:48
|
| NO I want something like SELECT INTOBecause I dont know the structure returned by the procedure |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-01 : 11:22:05
|
| How can you not know the structure return by the procedure? Does it use dynamic SQL to vary it's returned recordset?Raymond |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-01 : 11:25:41
|
| Is it a system provided procedure? (i.e. sp_helplogins) It returns multiple result sets. You could do:create table #tmp (col1 varchar(2000))insert #tmp exec master..xp_cmdshell 'osql -E -w2000 -Q "exec sp_helplogins"'To get it all into a single column but you would have to parse the results. |
|
|
|
ikhuram
Starting Member
28 Posts |
Posted - 2004-06-01 : 11:33:52
|
| Is there any other simple method as it requires extra effort of parsing. The stored procedure returns a recordset, and I want to create a temporary table based on that recordset. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-01 : 11:40:44
|
| I don't know of an easier way to do it than what we've described so far. You need to declare your structure and insert into it, even if you were parsing the single column result set described by kselvia.Raymond |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-01 : 11:43:05
|
| You could modify the sp to "select * into permanent_table from #workingtable where 1 = 2" to get an empty permanent deffinition of the table. Run it, then take that part back out. Then you could run select * into #temp from permanent_table insert #temp exec sp_procedure...One way or another, you have to have a structure of the result set. This is just a short cut to save typeing. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-20 : 02:04:17
|
| I figured out how to do this the other day and remembered someone (you) had asked about it, so here is a way:SELECT * into #tmp FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=myserver;', 'EXEC master.dbo.sp_who') Or create a linked server:sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = 'myserver'SELECT * into #tmp FROM OPENQUERY(LOCALSERVER,'master.dbo.sp_who') |
|
|
|
|
how to insert the results of a SP into a temp tbl