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         lt --- ERROR AT  TEMP ID   But I m getting the following error      Must declare the scalar variable   temp     What am I doing wrong

User · Answer

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

User · Answer

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

User · Answer

You ve declared  TEMP but in your insert statement used  temp    Case sensitive variable names     Change  temp to  TEMP

User · Answer

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

User · Answer

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

User · Answer

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 lt  fiddle demo

User · Answer

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

User · Answer

try the following query   SELECT ID     Name INTO  tempTable FROM Table  SELECT   FROM  tempTable WHERE ID   1   It doesn t need to declare table

[sql-server] Table variable error: Must declare the scalar variable "@temp"

Examples related to sql-server

Examples related to tsql

Examples related to table-variable