Thursday, March 22, 2007

Table variables in SQL Server 2005

In SQL Server 2000 there is an alternative to the use of temporary tables. We can use the table variables as an alternative to the table variable. Table variables store a set of records. The declaration syntax looks very similar to a CREATE TABLE statement
DECLARE @Blogs TABLE( BlogID int, BlogTitle varchar(100))
We can insert some data in the table variable very easily using the select statement to populate data in the table variables.
INSERT INTO @Blogs (BlogID, BlogTitle)
SELECT BlogID, BlogTitle FROM [Blog]Table
variables can be used in batches, stored procedures, User defined functions. We can also use the update and delete keywords with the table variables to modify or delete records.Here are some examples of working with the table variables.
UPDATE @Blogs
SET BlogTitle = ‘Changing the data of the blog Title’
WHERE BlogID = 6
DELETE FROM @Blogs
WHERE BlogID = 60
SELECT
TOP 5 *
FROM @Blogs

No comments: