store stored proc results into table
-
hi friends
is there any way we can store results of stored proc into
a temp table with creating table first.
something like
select exec(sp_who) into #temp
Thank you very much
-
if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#t_sp_who_output'))
drop table #t_sp_who_output
go
CREATE TABLE #t_sp_who_output (
[spid] [smallint] ,
[ecid] [smallint] ,
[status] [nchar] (30) ,
[loginame] [nvarchar] (128) ,
[hostname] [nchar] (128) ,
[blk] [char] (5) ,
[dbname] [nvarchar] (128) ,
[cmd] [nchar] (16)
)
GO
insert into #t_sp_who_output
exec sp_who
select * from #t_sp_who_output
My Blog:
http://dineshasanka.spaces.live.com/ -
Well can we directly execute a Stored Procedure inside a Cursor and Loop over the results returned by the executed Stored Procedure.
I don't know if this question is related in this context
Thanks
Prasad Bhogadi
www.inforaise.com -
Thank you very much Dinesh.
I am wondering is it posible without first creating the temp table first and insert data.
like select * into #temp
thanks
rajani
-
Exec Into requires that a table structure be there. It can't create it on its own.
-
Don't mean to mislead. there is no 'exec into' specifically, just the
insert into x
-
Thanks for the posts cmore
i got it 🙂
-
Not the fastest execution, but try ....
select * Into #Temp
from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'Set Nocount On Exec Master.dbo.sp_Who') A
Select * From #Temp
Once you understand the BITs, all the pieces come together
-
Thomas u r wonderful.
Thats exactly what i am after.
cheers
rajani
-
Neato
-
You can almost put this into the "trick" category. It has other ramifications...
The Good:
You can use this syntax inside UDFs to not only get around the UDF
contraint of not being able to call user SPs, but also around UDF
contraints such as not being able to use GetDate()
The not so good:
Does not work on all SPs or scripts
(yes, you could have an entire script instead of just an SP call)
You may have issues if called SP contains PRINT or other non-result set stuff
So use SET NOCOUNT ON either in SP, and/or prior to SP call...
Only one result set is allowed (not a bad thing, just needed to mention)
You may not get results if within the SP the result set is derived from either
a #Temp table of @Table var.
Don't get to discouraged if at 1st is does not work with your user SP.
SP may need to be "tweaked" to get results out of it correctly.
Another "sub-trick" is to use SET FMTONLY OFF just prior to EXEC SP
if you get error: "...The OLE DB provider 'SQLOLEDB' indicates that the object has no columns...."
Have fun
Once you understand the BITs, all the pieces come together -
Thats cool Thomas
Thanks
-
Also...
Do NOT use this in a loop or Scalar UDF. This method adds overhead, and should be used as a "last resort". Do not use this method just for the sake of using it, avoid it like you would cursors.
You will also not get information about the "remote" script within your query plan, it will just show "Remote Scan", so make sure the SP is optimized in itself, and put results in a #Temp table, then use the #Temp table to JOIN with others.
Another "sub-trick"... I've seen some strange things with OLEDB sometimes, so here are two "Variations" on the same theme.
-- OLEDB Variation
select * from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'SET FMTONLY OFF SET NOCOUNT ON Exec Master.dbo.sp_who')
-- ODBC Variation
Select * from OpenRowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes',
'SET FMTONLY OFF SET NOCOUNT ON Exec Master.dbo.sp_who')
Once you understand the BITs, all the pieces come together -
Thanks Thomas for ur detailed explanation.
i'll be using this for our internal purpose only.
thats should be cool
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply