deleting all records - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

deleting all records

Author

Topic

rajeshkumar77
Starting Member

46 Posts

Posted - 2002-02-14 : 23:20:31

Dear Friends,

In MS Access, if we want to delete all the records in a table, we can use
ctrl + a to select all tables and we can use del button to delete all
records at one time.

But in sqlserver enterprise manager, i cannot find this type of facility.

if anyone having idea on this, please participate on this discussion.

Thanks in advance.
Looking forward to hearing from you again.

Yours
Rajesh

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-14 : 23:28:18

Hi

What about

DELETE FROM Table

In Query Analyzer

Damian

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-02-14 : 23:29:01

You can hold down the shift button to highlight all records and then hit delete or alternatively use the truncate table or delete from T-SQL commands.

rajeshkumar77
Starting Member

46 Posts

Posted - 2002-02-15 : 00:24:10

Dear sir,
Thank you very much for your reply.
In my database table, there are 50,000 rows.
now i am deleting those using query analyzer.
however, is there any best way to delete all the reocrds in a easier way ?
Thanks in advance.
Looking forward to hearing from you again.
Yours
Rajesh

quote:

You can hold down the shift button to highlight all records and then hit delete or alternatively use the truncate table or delete from T-SQL commands.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-15 : 00:40:14

Query analyzer really is the best way. Once you get used to using it, you will find it a lot easier and quicker to do things in Query Analyzer than Enterprise manager.

Also, if you are dealing with a database on a remote server, Query Analyzer is MUCH quicker as it is not trying to sync the GUI with the remote database server on every click.

Damian

rajeshkumar77
Starting Member

46 Posts

Posted - 2002-02-15 : 01:30:11

Dear Mr.Damian,
Just now I tried to connect to remote server using query analyzer (using ip address and userid and password).
Amazing, query analyzer is connected within a fraction of seconds.
Thanks a lot.
Your reply helped me alot.
Millions of Thanks to you.

Yours
Rajesh

quote:

Query analyzer really is the best way. Once you get used to using it, you will find it a lot easier and quicker to do things in Query Analyzer than Enterprise manager.

Also, if you are dealing with a database on a remote server, Query Analyzer is MUCH quicker as it is not trying to sync the GUI with the remote database server on every click.

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-15 : 01:37:09

if you have to delete a lot of records you might want to do something like

set rowcount 1000
select 1
while @@rowcount > 0
delete tbl
set rowcount 0

This will stop it holding open a transaction and give the ytansaction log a chance to clear down.

The fatest way is 'truncate table tbl' but this is usually only available if you are not using transaction log backups.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Nazim
A custom title

1408 Posts

Posted - 2002-02-15 : 05:57:36

Hi ,

if you are looking to remove all the records from a database ,you can use sp_msforeachtable undocumented stored procedure. check this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12728 Spyder teaches about it.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources