{"id":370,"date":"2019-08-02T13:33:32","date_gmt":"2019-08-02T08:03:32","guid":{"rendered":"https:\/\/techieshouts.com\/?p=370"},"modified":"2022-08-09T19:06:56","modified_gmt":"2022-08-09T13:36:56","slug":"table-variables-in-ms-sql-server","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/table-variables-in-ms-sql-server\/","title":{"rendered":"Table Variables in MS SQL Server"},"content":{"rendered":"\n

Table variables in SQL Server are variables that store a set of records in a table like structure for a particular period of time. It is another way of storing data temporarily similar to temporary tables<\/a>. In this article, we will see what is table variable with an example.<\/p>\n\n\n\n

Generally, the name of the table variable should start with ‘@’ and followed by a meaningful name. Declaring the table variable just like declaring the local variables in SQL Server.<\/p>\n\n\n\n

Table Variable Syntax<\/h2>\n\n\n\n
DECLARE @TableVariableName TABLE \n(\n    column1 datatype, \n    column2 datatype\n);<\/pre>\n\n\n\n
For example, let’s populate\u00a0the list of Indian players from the table ‘tblPlayer’. <\/p>\n\n\n\n
DECLARE @PlayerDetails TABLE \r\n(playerID INT NOT NULL, playerName VARCHAR(50) NOT NULL);\r\n\r\nINSERT INTO @PlayerDetails\r\nSELECT playerID, FirstName\r\nFROM tblPlayer\r\nWHERE countryID = 1;\r\n\r\nSELECT * FROM @PlayerDetails;<\/pre>\n\n\n\n
Queries in the above code snippet, declares the table variable ‘@PlayerDetails’ with two columns, inserts the data into it and retrieves the data from it.<\/p>\n\n\n\n
Scope<\/h2>\n\n\n\n
The scope of the table variable ends as soon as it exits the batch of SQL statements. A function or stored procedure which contains table variable ends the scope once the execution is completed. <\/p>\n\n\n\n
Limitations<\/h2>\n\n\n\n