How to insert multiple rows in single insert statement?

Question

I have 5 text box contaning 5 contact no of a particular person. I want to insert those 5 contact no in tblContact(person_id|contact_no). Can I insert these 5 contact no in one sql statement OR I have to call a loop to insert 5 records?

Answer 1

INSERT INTO `tblContact` (`person_id`, `contact_no`) VALUES
('PERSON_ID_VALUE_1', 'CONTACT_NO_VALUE_1'),
('PERSON_ID_VALUE_2', 'CONTACT_NO_VALUE_2'),
('PERSON_ID_VALUE_3', 'CONTACT_NO_VALUE_3'),
('PERSON_ID_VALUE_4', 'CONTACT_NO_VALUE_4'),
('PERSON_ID_VALUE_5', 'CONTACT_NO_VALUE_5');

Answer 2

Many databases have a multi-row insert capability and MySQL is one of them. You can use (in 5.0+, not sure about earlier versions although a brief look at the 3.23/4.0/4.1 docs seems to indicate yes) something like:

insert into tblContact (person_id,contact_no) values
    (1, '555-5555'),
    (2, '555-1234');

More details here.

Aside: In genneral, if your database didn't support multi-row insert, you'd probably just use a transaction around the group of individual insert statements. We've found that multi-row inserts actually give us a pretty hefty speed increase (on our DBMS anyway - YMMV).

Answer 3

you can insert your 5 records in 1 insert statements.

like: insert into tblContact(person_id, contact_no) values(1,'145566'),(2,'233366'),(3,'564666')