473,835 Members | 2,295 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting multiple rows from an SQL Table

Hi All.

I have a routine that checks a SQL Table for all records 3 months
prior to a predetermined date, then I insert them into an Archive DB
then delete those records from the original table.

When I do a "select" for the records, I load them into a dataset, use
an "insert" statement to insert the info into the second table (by a
for next loop and using executenonquery , then a "delete" statement to
remove them.

The way I delete them is "loop" through the rows of the dataset,
create an SQL statement then use ExecuteNonQuery (see code below).

Surely, this is NOT the way to do it as most times, the record count
is around 70000-10000, and it seems to take quiet a while to do (maybe
about 3 records a second).

Could someone advise the most appropriate and speedy way to delete the
items. Remember the records are in a dataset, and I have read
somewhere that you can update/Delete/Insert into SQL using the
Dataset, but not sure of the command.

Any help would be much appreciated.

Thanks,
Robert

<---------------------- Code ----------------------->
Dim iloop as integer
Dim dsorderItems as new dataset

For iloop = 0 To dsOrderItems.Ta bles("t1").Rows .Count - 1
sSql = "delete tbl_OrderItems where OrderNum = '" &
dsOrderItems.Ta bles("t1").Rows (iloop).Item("O rderNum") & "'"

updCommand = New SqlCommand(sSql , SQLConn)
updCommand.Conn ection.Open()
updCommand.Exec uteNonQuery()
updCommand.Conn ection.Close()
Next
Nov 20 '05 #1
5 3790
2
The bit where you say you read you could do it with a dataset.... I think
you are referring to the dataadapter... I use sqldataadapter for some
stuff... mainly to drive datagrids... anyway, the dsqldataadapter spurpose is
to handle getting records from the database into a dataset and handling any
updates, inserts and deletes. From what I can see to delete a bunch of
records it just executes multiple delete statements similar to your code...
it does however provide other functions and validations that I am not too
clear on also so it does offer advantages apperntly...

Using a dataset allows you to do the operation on a local copy of the data
and then update the server... which is different to performing the updates
directly on the database server itself.

If you are using SQL Server then fire up the SQL Profiler tool... it'll let
you see every SQL command/stored proc being executed on the server... very
handy for seeing what the sqldataadapter does behing the scenes....

Cheers
"Robert Brown" <rb****@edium.c om> wrote in message
news:bd******** *************** *@posting.googl e.com...
Hi All.

I have a routine that checks a SQL Table for all records 3 months
prior to a predetermined date, then I insert them into an Archive DB
then delete those records from the original table.

When I do a "select" for the records, I load them into a dataset, use
an "insert" statement to insert the info into the second table (by a
for next loop and using executenonquery , then a "delete" statement to
remove them.

The way I delete them is "loop" through the rows of the dataset,
create an SQL statement then use ExecuteNonQuery (see code below).

Surely, this is NOT the way to do it as most times, the record count
is around 70000-10000, and it seems to take quiet a while to do (maybe
about 3 records a second).

Could someone advise the most appropriate and speedy way to delete the
items. Remember the records are in a dataset, and I have read
somewhere that you can update/Delete/Insert into SQL using the
Dataset, but not sure of the command.

Any help would be much appreciated.

Thanks,
Robert

<---------------------- Code ----------------------->
Dim iloop as integer
Dim dsorderItems as new dataset

For iloop = 0 To dsOrderItems.Ta bles("t1").Rows .Count - 1
sSql = "delete tbl_OrderItems where OrderNum = '" &
dsOrderItems.Ta bles("t1").Rows (iloop).Item("O rderNum") & "'"

updCommand = New SqlCommand(sSql , SQLConn)
updCommand.Conn ection.Open()
updCommand.Exec uteNonQuery()
updCommand.Conn ection.Close()
Next

Nov 20 '05 #2
Cor
Hi Robert,

I think that I when I was using the dataadapter I think I would do it like
this.

(I think I would not do that for this, because I think that I would just use
the datareader and a commandnonquery with a SQL delete, but this is your
choose)

You can use a dataview, first set the sortorder and than a rowfilter for the
dates.
(Use the sort because that we have seen in this newsgroup goes faster)

Than you can itterate through the dataview just as
\\\\
Dim dv As New DataView(ds.Tab les(0))
For i As Integer = dv.Count - 1 To 0 Step -1
dv(i).Delete()
Next
////
After this you can do a SQLdataadapter. update
You have to make for this an SQL deletecommand, or just use a cmdbuilder.

(You can of course also do that SQL non query delete in that dataviewloop.
Than I think it is better not to do the delete in the dataset but just
reread (fill) it again after that all deletes are done.)

I hope this helps?

Cor
Nov 20 '05 #3
Robert,

My dear friend, the answer is right in front of your eyes....

Why execute a query to delete every single record when you can execute a
query to delete ALL Records older than 3 months?, for example:
DELETE FROM Table1 WHERE YourFieldDate <='00/00/00'

To begin with, why go through all the records generating insert statements
when you could move them all in one shot with a single query?, for example:

select * into Table2 from Table1 WHERE YourFieldDate <= '00/00/00'

Good luck,

Juan.

"Robert Brown" <rb****@edium.c om> wrote in message
news:bd******** *************** *@posting.googl e.com...
Hi All.

I have a routine that checks a SQL Table for all records 3 months
prior to a predetermined date, then I insert them into an Archive DB
then delete those records from the original table.

When I do a "select" for the records, I load them into a dataset, use
an "insert" statement to insert the info into the second table (by a
for next loop and using executenonquery , then a "delete" statement to
remove them.

The way I delete them is "loop" through the rows of the dataset,
create an SQL statement then use ExecuteNonQuery (see code below).

Surely, this is NOT the way to do it as most times, the record count
is around 70000-10000, and it seems to take quiet a while to do (maybe
about 3 records a second).

Could someone advise the most appropriate and speedy way to delete the
items. Remember the records are in a dataset, and I have read
somewhere that you can update/Delete/Insert into SQL using the
Dataset, but not sure of the command.

Any help would be much appreciated.

Thanks,
Robert

<---------------------- Code ----------------------->
Dim iloop as integer
Dim dsorderItems as new dataset

For iloop = 0 To dsOrderItems.Ta bles("t1").Rows .Count - 1
sSql = "delete tbl_OrderItems where OrderNum = '" &
dsOrderItems.Ta bles("t1").Rows (iloop).Item("O rderNum") & "'"

updCommand = New SqlCommand(sSql , SQLConn)
updCommand.Conn ection.Open()
updCommand.Exec uteNonQuery()
updCommand.Conn ection.Close()
Next

Nov 20 '05 #4
Cor
Hi Juan,

That Robert wrote he first want to archive them.

But I agree with you that I would read them with a same where clause as
delete them, insert them in the archive and than delete them as you said.

But he said he wanted to use a dataset for that.

(I have given with this also the next answer)

:-)

Cor
Nov 20 '05 #5
Thanks to all that replied..

Juan and Cor have given me some real insight into how to perform the
operations. I was only putting it into a dataset because I thought
that might be the fastest, but I knew there was a better way.

I have tried out Juan's code and it certainly kills the timing of my
code.

Thank you all for the fast replies once again..

Robert


"Juan Romero" <ju*********@bo wne.com> wrote in message news:<ej******* *******@TK2MSFT NGP10.phx.gbl>. ..
Robert,

My dear friend, the answer is right in front of your eyes....

Why execute a query to delete every single record when you can execute a
query to delete ALL Records older than 3 months?, for example:
DELETE FROM Table1 WHERE YourFieldDate <='00/00/00'

To begin with, why go through all the records generating insert statements
when you could move them all in one shot with a single query?, for example:

select * into Table2 from Table1 WHERE YourFieldDate <= '00/00/00'

Good luck,

Juan.

"Robert Brown" <rb****@edium.c om> wrote in message
news:bd******** *************** *@posting.googl e.com...
Hi All.

I have a routine that checks a SQL Table for all records 3 months
prior to a predetermined date, then I insert them into an Archive DB
then delete those records from the original table.

When I do a "select" for the records, I load them into a dataset, use
an "insert" statement to insert the info into the second table (by a
for next loop and using executenonquery , then a "delete" statement to
remove them.

The way I delete them is "loop" through the rows of the dataset,
create an SQL statement then use ExecuteNonQuery (see code below).

Surely, this is NOT the way to do it as most times, the record count
is around 70000-10000, and it seems to take quiet a while to do (maybe
about 3 records a second).

Could someone advise the most appropriate and speedy way to delete the
items. Remember the records are in a dataset, and I have read
somewhere that you can update/Delete/Insert into SQL using the
Dataset, but not sure of the command.

Any help would be much appreciated.

Thanks,
Robert

<---------------------- Code ----------------------->
Dim iloop as integer
Dim dsorderItems as new dataset

For iloop = 0 To dsOrderItems.Ta bles("t1").Rows .Count - 1
sSql = "delete tbl_OrderItems where OrderNum = '" &
dsOrderItems.Ta bles("t1").Rows (iloop).Item("O rderNum") & "'"

updCommand = New SqlCommand(sSql , SQLConn)
updCommand.Conn ection.Open()
updCommand.Exec uteNonQuery()
updCommand.Conn ection.Close()
Next

Nov 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7862
by: rishi | last post by:
Looking for tips on how to improve performance on deleting records. In our database we do dataloads daily that require us to purge millions of records a day so any improvement in speed would be welcomed. CREATE OR REPLACE PROCEDURE ETL_CUSTATTRIB_STGTOTRG_ALT1v2 AS TYPE cust_t IS TABLE OF customer_master.customer_id%TYPE INDEX BY BINARY_INTEGER; TYPE attrib_t IS TABLE OF attribute_master.attribute_id%TYPE
7
5673
by: patrick.mcdonald | last post by:
Hello, Example: I have 100 rows that all have the field vaule of 'XYZ' I would like to delete only 10 of those rows. Is there such a query like "Delete 10 rows from table where table.field = 'XYZ'"
2
2207
by: Zak McGregor | last post by:
Hi all I have a table, for simplicity's sake containing one field, called unid. for example, select unid, oid from table gives me something like this: unid | oid ---------+---------
4
1656
by: Jim Michaels | last post by:
Is it safe to do what is below? deleting from a resultset while you are processing it? I don't know how dynamic the SQL database is. I assume you get a cursor to live dataset. Even if it is a cursor as opposed to a table, will the cursor's integrity be ruined if the current record is deleted? $q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link); while ($row2=mysql_fetch_array($q2)) { //do some stuff with resultset...
3
10733
by: manjub | last post by:
Hi, I am trying to add ability to delete multiple rows from a DataGridView in my application. When user selects multiple rows and presses delete button, I want to show a message asking to user to confirm delete. I am using UserDeletingRow event. My problem is that if 5 rows are, 5 different events are triggered. If I handle UserDeletingRow, I will have to ask for user's confirmation 5 times. Is there any way to avoid this and ask for...
10
2390
by: beam1985 | last post by:
So my problem lays in the $_POST if statement at the bottom operation, I keep getting Notice: Undefined index: delete and also Notice: Undefined index: checkbox. Please let me know if you have any ideas or suggestions im desperate to get this working, its part of my midterm project due tomorrow! Thank you so much $host="localhost"; // Host name $username="username"; // Mysql username $password="pass"; // Mysql password ...
2
1671
by: Brad Pears | last post by:
I am working on a new vb.net 2005 project using SQL server 2000 as the backend db. I have a listview where control I want a user to be able to select either just one or multiple rows in the listview and perform an action on them. If the user chooses to delete these rows I wonder what the best way to handle the deletion of multiple rows is... Right now I have a stored procedure for deleteing jsut opne row. i.e delete from {tablename}...
6
8455
by: =?Utf-8?B?TU1TSkVE?= | last post by:
How to let user delete multi rows from the BindingSource while the SelectionMode Property set to RowHeaderSelect I have in my program datagridview bound it to sql table Throw Bindingsource To fill it: MyTableTableAdaptor.fill(MyDataset.Mytable);
3
3934
by: nigelesquire | last post by:
Please help! I'm trying to clone and delete multiple rows with JavaScript. I need two delete buttons that work...! I only have one for now, but it's not working properly, the output count is messing up. Problems:
0
9803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10560
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10233
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9345
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7766
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6966
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5636
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5804
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.