I am trying to achieve:
declare @TEMP table (ID int, Name varchar(max))
insert into @temp SELECT ID, Name FROM Table
SELECT * FROM @TEMP
WHERE @TEMP.ID = 1 <--- ERROR AT @TEMP.ID
But I'm getting the following error:
Must declare the scalar variable "@temp".
What am I doing wrong?
This question is related to
sql-server
tsql
table-variable
You should use hash (#) tables, That you actually looking for because variables value will remain till that execution only. e.g. -
declare @TEMP table (ID int, Name varchar(max))
insert into @temp SELECT ID, Name FROM Table
When above two and below two statements execute separately.
SELECT * FROM @TEMP
WHERE @TEMP.ID = 1
The error will show because the value of variable lost when you execute the batch of query second time. It definitely gives o/p when you run an entire block of code.
The hash table is the best possible option for storing and retrieving the temporary value. It last long till the parent session is alive.
If you bracket the @ you can use it directly
declare @TEMP table (ID int, Name varchar(max))
insert into @temp values (1,'one'), (2,'two')
SELECT * FROM @TEMP
WHERE [@TEMP].[ID] = 1
There is one another method of temp table
create table #TempTable (
ID int,
name varchar(max)
)
insert into #TempTable (ID,name)
Select ID,Name
from Table
SELECT *
FROM #TempTable
WHERE ID = 1
Make Sure You are selecting the right database.
You could stil use @TEMP
if you quote the identifier "@TEMP"
:
declare @TEMP table (ID int, Name varchar(max));
insert into @temp SELECT 1 AS ID, 'a' Name;
SELECT * FROM @TEMP WHERE "@TEMP".ID = 1 ;
Either use an Allias in the table like T and use T.ID, or use just the column name.
declare @TEMP table (ID int, Name varchar(max))
insert into @temp SELECT ID, Name FROM Table
SELECT * FROM @TEMP
WHERE ID = 1
You've declared @TEMP but in your insert statement used @temp. Case sensitive variable names.
Change @temp to @TEMP
try the following query:
SELECT ID,
Name
INTO #tempTable
FROM Table
SELECT *
FROM #tempTable
WHERE ID = 1
It doesn't need to declare table.
Source: Stackoverflow.com