0

I would fill a table from a query in a stored procedure,

This works:

SELECT * 
INTO #tmpTable 
FROM MyTable

This works:

SELECT TOP (1) * 
FROM MyTable 
WHERE Land = @Land

but how do I fill #tmpTable with 

SELECT TOP (1) * 
FROM MyTable 
WHERE Land = @Land
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MeerArtefakt
  • 386
  • 2
  • 6
  • 26
  • `SELECT TOP (1) * INTO #tmpTable FROM MyTable WHERE Land = @Land`? This question might be helpful: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure?rq=1 – jpw Oct 21 '14 at 09:49
  • @MeerArtefakt, what exactly are you looking for? Can you describe your problem statement clearly? – Manoj Pandey Oct 21 '14 at 10:38
  • @Manoj: I would see the #tmpTable from my stored procedure as a return value. later i would work with the table from my c# code – MeerArtefakt Oct 21 '14 at 10:42
  • @jpw: it works fine! but why I can't see the table after executing the the stored procedure? Do I need "SELECT * from #tmpTable" after "SELECT TOP (1) * INTO #tmpTable FROM MyTable WHERE Land = @Land" ? – MeerArtefakt Oct 21 '14 at 10:45
  • You want to get records stored in your #temp table from the SP in the end, right? In this case you can do a "SELECT * from #tempTable" in the end of the SP, which will return all records after the SP execution completes. – Manoj Pandey Oct 21 '14 at 10:45

2 Answers2

0

Because the #temp Table's scope is limited to its session (SPID), i.e. the Stored Procedure itself. After the SP execution completes the #temp table is Dropped.

Also while the SP is being executed you cannot see the #temp Table from other sessions (SPID)

Manoj Pandey
  • 1,307
  • 12
  • 14
  • perhaps suggest returning the temp table into a table variable output parameter using `select * from #temp` as a way to get at the data from the calling code or if required use a ##globalTempTable. – Tanner Oct 21 '14 at 10:34
0

USE Global temp table like ##temp even it can be accessible after execution of sp also

Rathnam
  • 1