SQL Server Performance Forum – Threads Archive

Exec

Hi,
I am trying to insert records retrived by using stored Procdure
I used the following Create table #t (userId int not null,col2 varchar(100),col3 varchar(100))
Insert into #t exec(‘tempProc ”tt”’ )
Select userId from #t
drop table #t It works well only If I define the table structure. When I tried for Select * into #t from exec(‘tempProc ”tt”’ )
Select userId from #t
drop table #t I am getting the error Incorrect syntax near the keyword ‘exec’.
How to overcome this? Madhivanan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">When I tried for Select * into #t from exec(‘tempProc ”tt”’ ) Select userId from #t drop table #t I am getting the error Incorrect syntax near the keyword ‘exec’. How to overcome this?<hr height="1" noshade id="quote"></blockquote id="quote">Try this: <blockquote id="quote">quote:<hr height="1" noshade id="quote">Create table #t (userId int not null,col2 varchar(100),col3 varchar(100)) Insert into #t exec(‘tempProc ”tt”’ ) Select userId from #t drop table #t<hr height="1" noshade id="quote"></blockquote id="quote"><img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I’m only half-joking. You have the solution. It is not good idea to use select into anyway, except when you add false condition in where cluse (e.g. 1=0). While select into is run, temp db is locked. At least it was true last time I checked. Why do you use temp stored proc? I bet satya or someone else will come with link about temp stored procs and their drowbacks. I have never used them but I remember article saying they are evil <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I have been looking at how to do this for some time. We do a lot of queries across servers, which can be real slow, as they seem to return all the data and then filter and join it on the machine you are running on. Instead, if you can write a stored procedure on the target machine, passing parameters as needed, then the syntax – Insert into table #t
exec serverlink.database.dbo.storedprod ‘p1′,’p2’ then the performance increase is fantastic. I went from 2.5 minutes to 2.5 seconds with a recordset of 11 rows, and from 2.5 million reads locally to 159. Thanks for discussing this topic, as I was able to Google it to here. Geoff

i.m.h.o. select into is sloppy and lazy. I have likewise never, ever found a use for temporary stored procedures. Tom Pullen
DBA, Oxfam GB

]]>