473,735 Members | 5,287 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Best way to issue hundreds of inserts/updates???

Using mysql 4.0.23-

What is the best way to execute several (hundreds of) inserts and updates?
Rather than issuing tons of individual inserts and updates, can I send the
strings to a text file and then have mysql do them all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??

Any help appreciated...s yntax also appreciated :-)
Craig
Jul 23 '05 #1
1 1885
"Craig Stadler" <cs********@hot mail.com> wrote in
news:fW******** **************@ news.easynews.c om:
Using mysql 4.0.23-

What is the best way to execute several (hundreds of) inserts and
updates? Rather than issuing tons of individual inserts and updates,
can I send the strings to a text file and then have mysql do them
all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);
yes, thats a good idea. it really mirrors the functionality and syntax of
a mysql dump file.

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??


go to your command prompt...

mysql -u <username> -p < query.txt
then you'll be prompted for your password, and a second or two later, your
updates will be done.
Jul 23 '05 #2

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

Similar topics

2
9370
by: Tim Callaghan | last post by:
We have an inhouse replication process which is causing us headaches when we try to run more than one copy of it against the same target database (we support unlimited remote databases so we may have to process in 20 replication files on a Monday morning). The issue I'm seeing is that computer 1 starts processing in the inserts/updates and continues through the process. Computer 2 starts processing inserts/updates fine as well but at...
2
2064
by: L. Blunt | last post by:
Hopefully someone can at least point me in the right direction for more research (e.g.: correct terminology). My only previous experience was just dumping data into a database using ODBC, and that was some years ago so now mostly forgotten. I need to write an NT Service/Application (in C/C++) that will be getting data sent to it via SQL Server 2000. The data will arrive in my SQL Server (read-only access), via replication of tables from...
3
3048
by: Viswanatha Thalakola | last post by:
Hello, Can someone point me to getting the total number of inserts and updates on a table over a period of time? I just want to measure the insert and update activity on the tables. Thanks. - Vish
1
1333
by: TammyZ | last post by:
I have developed an Access 2000 application: front-end as a run-time on user's desktops, and the back-end database on the server. I need help with ensuring that all the code that I have programmed to occur on a command button click is happening. For example, a crucial area is when a packing list is created. Many things need to occur when this is created, such as printing 2 copies of the packing list, creating a record that says an...
0
1116
by: Steve - DND | last post by:
Is there a way to perform multiple inserts and updates to SQL Server with stored procedures and the Command object's named parameters all at once? The only way to do this that I'm gathering so far is to either run each insert/update individually, or to write out each SP execution string with it's parameters separated by semi-colons, and run it that way. Any help would be appreciated. Thanks, Steve
2
1573
by: raisinodd | last post by:
Sorry for cross posting, just realised myodbc wasn't the place for this: Hello, I'm having a problem with records inserted using NOW() as data in a field having that timestamp updated when I alter another field. I am using MySQL version 5.0.20-nt. To replicate: use test;
0
997
by: Dimitris Milonas | last post by:
Hello to all, I have a DetailView on a page with Insert/Update enable and Paging enable. I would like to have on the same page two buttons ("Submit" and "Cancel") where with the first ("Submit") all the entries will be passed to the database (Inserts - Updates - Deletes) while with the second ("Cancel") all changes will be discarded. Is it possible? I' am using DataSet and DataAdapter and I have to execute Adapter.Update(DataTable)...
4
6576
by: shreyask | last post by:
I have been working on doing bulk updates and inserts through sqlapi++ using a bulk udpate extention library, developed, a long time ago, internally. The database is oracle. The bulk inserts/updates are done in batches of 50K. I am facing a very peculiar performance issue with regard to these bulk operations. The initial batch of bulk insert (50K records) is finished within 3 seconds; while the next similar batch for bulk insert or update...
0
1407
by: 1477v | last post by:
Hi, I've written a couple adapter type applications to grab insert, update and delete operations on desired tables of my DB2 database. One application is based on installing and using triggers on the desired tables to capture the inserts,updates and deletes as they occur. The second and more recent application reads the UDB database logs using the db2ReadLog API function. Both of these methods work well but I have found that...
0
8959
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...
0
9462
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8199
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
6747
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
6049
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
4558
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
4821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2738
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2187
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.