Re: How does a multi-row INSERT work?
Ok. I believe you got your answer... for the syntax... I just want to add that this is faster because... using this you only modify the index file once. Lets see for: INSERT INTO x VALUES (a,b); INSERT INTO x VALUES (c,d); The server does: open table INSERT INTO x VALUES (a,b); update index close table open table INSERT INTO x VALUES (c,d); update index close table But for INSERT INTO x VALUES (a,b),(c,d); The server does: open table INSERT INTO x VALUES (a,b),(c,d); update index close table Now it's clear why multi-row INSERT is faster. Of course THIS IS a faulty explanation (grosso modo in latin) but show somehow what's going on ! Gabriel PREDA - Original Message - From: Chris W. Parker [EMAIL PROTECTED] Subject: How does a multi-row INSERT work? Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How does a multi-row INSERT work?
Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
Chris W. Parker wrote: Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. I think you're looking for something like INSERT INTO table [ (x, y, z) ] VALUES (a, b, c), (d, e, f), (h, i, j); -- Jonathan Wright mail at djnauk dot co dot uk Life has no meaning unless we can enjoy what we've been given -- Running on Gentoo Linux (2.6.10-gentoo-r7-djnauk-b03 i686 AMD Athlon(tm) XP 2100+ GNU/Linux) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
In the last episode (Mar 31), Chris W. Parker said: I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. The syntax is described in the manual, but not with an explicit example. One of the user comments gives an exaple. http://dev.mysql.com/doc/mysql/en/insert.html INSERT INTO mytable (f1,f2,f3) VALUES (1,2,3),(4,5,6),(7,8,9); will insert three rows. Just tack as many ,(...) clauses on the end as you want. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
INSERT INTO table (field1, field2) VALUES (1, 2), (3, 4), (5, 6), (7,
8);
That would insert 4 rows first row with field1=1, field2=2, second
field1=3, field2=4, etc.
This is documented on the INSERT Syntax page of the manual, but it may
be kind of hard to read for a beginner as it just says VALUES({expr |
DEFAULT},...),(...),...
On Thu, 2005-03-31 at 11:46 -0800, Chris W. Parker wrote:
Hello,
I searched the archives, looked through the manual, and searched google
for info on how to actually perform a multi-row INSERT but didn't find
an answer.
Would someone please show me the syntax for this please?
I could just do a loop and INSERT the data that way but according to the
manual, a multi-row INSERT is faster.
Thanks,
Chris.
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How does a multi-row INSERT work?
John McCaskey mailto:[EMAIL PROTECTED]
on Thursday, March 31, 2005 12:04 PM said:
This is documented on the INSERT Syntax page of the manual, but it may
be kind of hard to read for a beginner as it just says VALUES({expr |
DEFAULT},...),(...),...
Oooh... In fact I did look through those syntax expressions and din't
notice it. Of course I was looking for a keyword like MULTI or
something.. :\
Thanks everyone!
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
Dan Nelson [EMAIL PROTECTED] wrote on 03/31/2005 03:01:45 PM: In the last episode (Mar 31), Chris W. Parker said: I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. The syntax is described in the manual, but not with an explicit example. One of the user comments gives an exaple. http://dev.mysql.com/doc/mysql/en/insert.html INSERT INTO mytable (f1,f2,f3) VALUES (1,2,3),(4,5,6),(7,8,9); will insert three rows. Just tack as many ,(...) clauses on the end as you want. -- Dan Nelson [EMAIL PROTECTED] One warning they don't mention is that the longest statement you can make determined by the server's max_allowed_packet variable. You can add as many ,(...) sets into your statement as you like so long as you don't make a statement that's too big. To ask your server what it's value is, run this command: SHOW VARIABLES LIKE 'max%'; max_allowed_packets will be one of the values listed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How does a multi-row INSERT work?
Chris W. Parker writes: I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. The basic syntax is to separate the (...) with commas (,) ala: create table table1 (sku int, title varchar (20)); insert into table1 (sku, title) values (1,'A'), (2, 'B'); Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
There are two forms of multi-row insert. Here is a script that illustrates both: use tmp; drop table if exists target; create table if not exists target (id smallint not null, name char(10) not null, primary key(id)); insert into target (id, name) select empno, lastname from Sample.Employee where workdept = 'D21'; select * from target; insert into target (id, name) values (500, 'Smith'), (600, 'Jones'); select * from target; The first insert statement copies specified rows and columns from another table into 'target'. The second insert statement creates multiple new rows in the 'target' table from scratch. Rhino - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 31, 2005 2:46 PM Subject: How does a multi-row INSERT work? Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
