you have to create the table explicitly, and then insert into the table; you cannot take advantage of the INTO #temp without jumping through extra hoops using openquery / openrowset
CREATE TABLE #Emp(EmpID int, EmpName varchar(30) , State varchar(2) )
INSERT INTO #Emp(EmpID ,EmpName,State)
openquery/rowset requires a static string, so you cannot pass paarameter s to a proc without resorting to dynamic sql
SELECT * INTO #emp
FROM OPENROWSET( 'SQLNCLI',
'SET FMTONLY OFF; SET NOCOUNT ON; exec Db_Name.dbo.sp_Get_Employees'
I would like to execute a stored procedure and have its results sent to a temp table so that I could do further actions on those results. For example, I could run this:
which could result in something like this:
EmpID EmpName State
100 Adams NY
101 Baker RI
102 Charles NY
103 Davis NJ
104 Edwards NY
I don't know the correct syntax but I would like to process something like this:
execute sp_Get_Employees into #Emps
select * from #Emps where State = 'NY'
Please let me know your suggestions for a valid set of statements that could do this type of processing. Thank you for your help.
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!