Asger Blond
ab-mi at post3.tele.dk
Tue Jul 8 14:13:26 CDT 2008
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