Clear data from table without deleting a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Clear data from table without deleting a table

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2011-05-13 : 02:42:16

Hi,

I am using query "DELETE * FROM Table_EPAR_Database;" and its giving me an error message "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.".

Don't know where the problem is.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-13 : 02:47:04

[code]DELETE Table_EPAR_Database[/code]

or

[code]DELETE FROM Table_EPAR_Database[/code]

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-13 : 06:45:52

[code]TRUNCATE TABLE Table_EPAR_Database[/code] will also work, and will reset any identity field you may have.
However, it does not write anything to the transaction log, so it is an unrecoverable delete.

We won't mention anything about prefixing table names with "Table_"

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-13 : 07:02:57

quote:
However, it does not write anything to the transaction log, so it is an unrecoverable delete

Strictly speaking, it is a minimally-logged operation, and can be rolled back in an explicit transaction:

CREATE TABLE Test (a int)
INSERT Test VALUES(1)
BEGIN TRANSACTION
TRUNCATE TABLE Test
SELECT * FROM Test
ROLLBACK TRANSACTION
SELECT * FROM Test
DROP TABLE Test

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-13 : 07:19:07

Well sure, inside a transaction. Why would we use a transaction when we prefix our table names with "Table_"?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-13 : 07:20:59

I agree, you've got me there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources