[dba-SQLServer] Truncating Temporary Tables

Thanks - Yes you are correct about the wrong terminology I used.

I have read that table variables are more 
efficient than temporary tables.  I have go 
around the problem of not being able to truncate 
by using variables to track the first and last values of the RowID.

David

At 07/9/2008, you wrote:
>David,
>
>Yes, it's possible to truncate a TEMPORARY TABLE, but it is not possible to
>truncate a TABLE VARIABLE which is what you are using...
>I don't think you can reseed the identity value of a column in a table
>variable.
>Using a temporary table instead you can try this reseeding:
>
>CREATE TABLE #ttmpGoalIns (RowID int IDENTITY (1, 1) NOT NULL, GoalID int,
>Description varchar(100), GoalAmt money DEFAULT (0) )
>INSERT #ttmpGoalIns (GoalID,Description) values(10,'a')
>INSERT #ttmpGoalIns (GoalID,Description) values(20,'b')
>SELECT * FROM #ttmpGoalIns
>TRUNCATE TABLE #ttmpGoalIns
>INSERT #ttmpGoalIns (GoalID,Description) values(30,'c')
>SELECT * FROM #ttmpGoalIns --> RowID reseeded to 1
>DROP TABLE #ttmpGoalIns
>
>Asger
>
>
>
>-----Oprindelig meddelelse-----
>Fra: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af David
>Emerson
>Sendt: 8. juli 2008 19:56
>Til: dba-SQLServer at databaseadvisors.com
>Emne: [dba-SQLServer] Truncating Temporary Tables
>
>Is it possible to truncate a temporary table in SQL2000?
>
>I have a table defines as follows:
>
>DECLARE @ttmpGoalIns TABLE (RowID int IDENTITY (1, 1) NOT NULL,
>GoalID int, Description varchar(100), GoalAmt money DEFAULT (0) )
>
>During my code I want to delete the rows and reset the identity back
>to 1.  Truncate is supposed to do this but I get an Incorrect Syntax
>error on the line:
>
>TRUNCATE TABLE @ttmpGoalIns
>
>
>Regards
>
>David Emerson
>Dalyn Software Ltd
>Wellington, New Zealand
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com