Table variable error: Must declare the scalar variable "@temp"

Question

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?

Iodide

SELECT * FROM @TEMP T WHERE T.ID = 1 – Altagraciaaltaic

A table variable is inherently temporary -- there is no such thing as a "temporary table variable". What you are trying to do is declare a table variable. There is also a related but different concept of a temporary table. – Funda

Answer 1

A table alias cannot start with a @. So, give @Temp another alias (or leave out the two-part naming altogether):

SELECT *
FROM @TEMP t
WHERE t.ID = 1;

Also, a single equals sign is traditionally used in SQL for a comparison.

Mcnamee

Answer 2

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 

Icarus

Answer 3

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.

Langer

Answer 4

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

Christianson

Answer 5

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.

Capreolate

Answer 6

try the following query:

SELECT ID,
   Name
INTO #tempTable
FROM Table

SELECT *
FROM #tempTable
WHERE ID = 1

It doesn't need to declare table.

Loyalty

Answer 7

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 ;   

db<>fiddle demo

Pianist

Answer 8

You've declared @TEMP but in your insert statement used @temp. Case sensitive variable names.

Change @temp to @TEMP

Equation

Variables are NOT case sensitive. – Arlberg