Reset auto incrementing number

[jgurgen]

i have an int field that auto increments by 1 in my temp table. How do i reset this number so i can start fresh everytime i use the temp table?

 

[gmmastros]

In Books On Line, look for DBCC CHECKIDENT

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Another option is to truncate your temp table rather than deleting from it, or even recreating the temp table each time. It all depends what you are doing with the temp table. You can't truncate if a foreign key relationship is involved, but I doubt it is on a temp table.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jgurgen]

i used the auto gen sql script from enterprise but it doesnt reset the primary keys on loanid, historycounter, seq id and it doesnt set the default value of my seqid to 1

	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[iSplit_Disb_TempTran]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[iSplit_Disb_TempTran]
	GO
	PRINT 'CREATING TEMP TABLE'
	CREATE TABLE [dbo].[iSplit_Disb_TempTran] (
		[LoanID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
		[HistoryCounter] [smallint] NOT NULL ,
		[SeqID] [tinyint] NOT NULL ,
		[TranDate] [datetime] NULL ,
		[TranCode] [smallint] NULL ,
		[TranAmt] [money] NULL ,
		[TranType] [bit] NULL ,
		[Used] [bit] NULL ,
		[ClosingDate] [datetime] NULL ,
		[DaysOpen] [int] NULL ,
		[InterestAccrued] [money] NULL ,
		[SysGen] [bit] NULL ,
		[Balance] [money] NULL 
	) ON [PRIMARY]
	GO

 

[SQLDenis]

go to the options tab and check script Primary Keys. FK, Defaults, constraints

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLSister]

If this is truly a temp table you should make it a real temp table not create it as a table in your database. Real temp tables start with # or ## or you can often use table variables for performance.

Questions about posting. See faq183-874

 

[SQLSister]

And Oh I forgot - what autoincrementing field? I see no identity field in your table definition. Nor any primary keys or default values, etc. You have to script those things as well if you want to drop and recreate a tble. But truly this is a bad idea. Use a rela temp table instead especially if multiple people will run the proc and could run into each other.

Questions about posting. See faq183-874