Using @@ROWCOUNT and TABLE Variables for Database Interactions with Transact-SQL - Filling a TABLE variable with existing rows
(Page 4 of 4 )
We are not allowed to use the SELECT..INTO statement to work with TABLE variables. To transfer the rows from a SELECT to a TABLE variable, the only option we have is to use INSERT with SELECT. The following is a sample script:
use northwind
set nocount on
go
declare @t_emp TABLE
(
ID int identity(1,1)primary key,
EmpID int unique,
Ename varchar(30) not null
)
insert into @t_emp(EmpID, Ename)
select EmployeeID, LastName + ' ' + FirstName
from Employees
select * from @t_emp
go
The most important statement of the above script is the following:
insert into @t_emp(EmpID, Ename)
select EmployeeID, LastName + ' ' + FirstName
from Employees
It clearly states that the output of the SELECT statement should be transferred to the table variable @t_emp. But while transferring we have to make sure that the number of columns along with data types should match.
In the next upcoming article, I shall concentrate more on tables, loops, cursors, and more. So, please check back frequently or sign up for a newsletter! Any bugs, errors, improvements etc., are highly appreciated at http://jagchat.spaces.live.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
