SQL SERVER – Table Variables

             SQL SERVER – Table Variables             

VARIABLES:-

                Variables are used to store data temporarily only for a single session. While declaring a Variable we have to place @ prior to variable name.

SCOPE:- 
         we have to execute from the Declare statement to till end then only commands will execute otherwise we will get error during execution. So we use Variables only with in a created session

Example:-

DECLARE @A INT

SET @A=100

TABLE VARIABLES:-

                         Like a Variable Table Variable is also used to store data temporarily. If you declare a Variable with Table datatype then it is called as Table Variable.

SCOPE:- 
           we have to execute from the Declare statement to till end then only commands will execute otherwise we will get error during execution. So we use Variables only with in a created session

.

Example:-

DECLARE @Table_variable TABLE(ID INT , SAL MONEY, LOC VARCHAR(100))

SELECT * FROM @Table_variable

DML Operations:-

                 We can perform all DML operations on Table Variables.

Example:-

DECLARE @Table_variable TABLE(ID INT , SAL MONEY, LOC VARCHAR(100))

INSERT INTO @Table_variable  Values(23, 23000, 'BANGALORE')

INSERT INTO @Table_variable  Values(24, 23000, 'CHENNAI')

INSERT INTO @Table_variable  Values(25, 23000, 'HYDERABAD')

SELECT * FROM @Table_variable