Truncate Table Table variable

[Rahul]

Hi,
Sql Server 2005
I am using table variable with in a stored procedure and within a
table variable i am using identity field.
Sometimes I required to truncate the table variable to rseed the
identity, but truncate table variable, dbcc checkident are not
working.

Any idea...

Declare @Columns Table(id Int Identity(1,1), [Name] VarChar(155));
Insert Into @Columns([Name])
Select 'A'
Union All
Select 'B'
Select * From @Columns
Truncate Table @Columns

Insert Into @Columns([Name])
Select 'X'
Union All
Select 'Y'
Select * From @Columns

Rahul

[Balaji]

Hi Rahul,

Truncate table and dbcc checkident are not supported on table varible. Use
temp table instead.

Regards, Balaji
"Rahul" <verma....@gmail.com> wrote in message
news:2e795776-04b1-40e0...@u25g2000prh.googlegroups.com...

[Phil]

Hi Tahul

Rather than using Truncate use DELETE FROM.

Thanks Phil

"Rahul" wrote:

> .
>

[Dan Guzman]

> Rather than using Truncate use DELETE FROM.

But note that Rahul's requirement is to also reseed the identity and DELETE
won't do this. I think he will need to instead use a standard temp table so
that TRUNCATE can be used.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/