need help w/ simple DELETE Query in SQL Server

[santosh1]

I want to delete all the records in the table without using the where clause. How can I delete all records from table in SQL Server? In access you could do,

Delete * from tblEmployee.

Is there similar way in SQL Server? Thanks. S.

 

[DavidSL]

Truncate Table tablename

 

[santosh1]

Thanks DavidSL.

 

[RiverGuy]

DELETE FROM tblEmployee

That will delete all your records.

 

[MDXer]

there is one big difference between

DELETE FROM tblEmployee

and

TRUNCATE TABLE tblEmployee

The diffrence is if there is an identity column how it will behave. the Delete will not reset any columns that are identity columns so if you have a Primaray Key that is an identity column you numbers will continue to increase from the max value when the delete occured.

example:
before delete
IDCol
-----
1
2
3
4
5

after delete:
IDCol
-----
6
7
8
9
10

this can have a negative impact if this ID is used as a key in any child tables.

The truncate command will reset the ID value so the identity column will always start at the base value specified for the identity.
example
before truncate
IDCol
-----
1
2
3
4
5

after truncate
IDCol
-----
1
2
3
4
5


The Delete will also allow you to target specific records from a table

DELETE FROM tblEmployee where employeeName = 'John Smith'

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[RiverGuy]

Another difference is that the TRUNCATE statement does not log an entry in the transaction log for each row that is removed, whereas DELETE does.