Clearing all data from a table.

[mike55]

Hi all

 

I am currently experimenting with FitNesse, which is an automated acceptance testing tool. Now to run my fit test, I have to clear my database of all the data. I am therefore using "Delete From <<table>>", and replacing <<table>> with the name of the table. Some of my tables have an id column that is auto-incrementing. My questions is therefore, can I reset the column id back to 0 using sql code?

 

Mike55.

[Gill Bates]

I'm pretty sure the only way you can do this is by dropping the column and then adding it again.

ALTER TABLE dbo.MyTable DROP COLUMN MyIdentityCol
ALTER TABLE dbo.MyTable ADD MyIdentityCol int NOT NULL IDENTITY (0, 1)

[mike55]

But will that affect the relationships I have set-up between my datatables?

 

Mike55

[IUnknown]

Try something like

DBCC CHECKIDENT('<<table>>', RESEED, 0)

I used 0 as the seed, of course you may use another value to reseed (same as what you used originally).

 

Hope this helps!

[Gill Bates]

Oh, nice. I didn't know about that function.

[mike55]

Genius...What else can I say...Thanks IUnknow.

 

Mike55.