Declare Table Variable in SQL Server

It is a very useful concept. We can do INSERT, UPDATE, DELETE of table variable.

DECLARE @VarEmpTbl TABLE

(

EmpId bigint,     — here we have declare column with Datatype

EmpName varchar(50),

DeptId Int

)

Now we can do Insert to table variable ‘@VarEmpTbl’

Insert into @VarEmpTbl (EmpId,EmpName,DeptId) select ID,Name,Deptid from EmpDtl where DeptID=1

Now we have inserted the values where DeptID=1. Let’s we see the output

select * from @VarEmpTbl

Output:

Id  Name       Deptid

—————————

1   Pritam          1
2   Rintu Das     1
3   Runa sarkar  2
7   Pritam          1
8   Pritam          1
9   Pritam          1

Now we will go for Update

update @VarEmpTbl set EmpName=EmpName+’ Das’ where EmpId <> 2

so here string called ‘ Das’ will be add to all the Name column except EmpId=2

Let’s see the output: 

select * from @VarEmpTbl

Id  Name       Deptid

—————————

1   Pritam Das        1
2   Rintu Das          1
3   Runa sarkar      2
7   Pritam Das        1
8   Pritam Das        1
9   Pritam Das        1

Now will go for Delete

Delete from @VarEmpTbl where EmpId in (7,8,9)

select * from @VarEmpTbl

Id  Name       Deptid

—————————

1   Pritam Das        1
2   Rintu Das          1
3   Runa sarkar       2