Dropping a Temp Table

• Rog Saber

  SSCrazy

  Points: 2548

  More actions

  April 19, 2011 at 9:29 am

  #235811

  In a stored proc, I am creating a temp table:

  DECLARE @TempResults TABLE

  (row_num smallint identity,

  rate_split char(1),

  fetch_col char(1),

  calculation_needed char(1))

  I am using the row_num column as a 'key'. I am populating the table with a cursor but I want to clear out the table each time. When I delete * from @TempResults, the row_num does not reset. If I state drop table @TempResults, I get an error.

  Basically, I just want to clear out the table, populate with some data, clear it again and so on.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 19, 2011 at 9:43 am

  #1313463

  Use a temp table (#tbl)

  Truncate it and it'll reseed.

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  April 19, 2011 at 10:47 am

  #1313516

  Rog Saber (4/19/2011)

  ---

  In a stored proc, I am creating a temp table:

  DECLARE @TempResults TABLE

  (row_num smallint identity,

  rate_split char(1),

  fetch_col char(1),

  calculation_needed char(1))

  I am using the row_num column as a 'key'. I am populating the table with a cursor but I want to clear out the table each time. When I delete * from @TempResults, the row_num does not reset. If I state drop table @TempResults, I get an error.

  Basically, I just want to clear out the table, populate with some data, clear it again and so on.

  You have use declare and not create so it is Table Variable(@) ,you need to use Temporary Table(#) inorder to reset the identity value and you can't drop Table Variable (@)

  Have a look at this link

  http://qa.sqlservercentral.com/articles/Table+Variables/63878/

  Thanks
  Parthi