Need any script/techniques for inserting multiple rows in mysql table

Question

I need to insert around 500-1000 rows into a table in mysql database.Here is the sample insert query for my table

INSERT INTO `jtbillingtest`.`at_user`
(`clientGuid`,
`clientFirstName`,
`clientMiddleName`,
`clientLastName`,
`gender`,
`clientAddress`,
`clientMobileNumber`,
`clientEmailID`,
`clientTypeCode`,
`active`)
VALUES
(
<{clientGuid: }>,
<{clientFirstName: }>,
<{clientMiddleName: }>,
<{clientLastName: }>,
<{gender: }>,
<{clientAddress: }>,
<{clientMobileNumber: }>,
<{clientEmailID: }>,
<{clientTypeCode: }>,
<{active: Yes}>
);

And My table structure is

CREATE TABLE `at_user` (
  `clientGuid` int(11) NOT NULL AUTO_INCREMENT,
  `clientFirstName` varchar(45) DEFAULT NULL,
  `clientMiddleName` varchar(45) DEFAULT NULL,
  `clientLastName` varchar(45) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `clientAddress` varchar(45) DEFAULT NULL,
  `clientMobileNumber` varchar(45) NOT NULL,
  `clientEmailID` varchar(45) DEFAULT NULL,
  `clientTypeCode` varchar(2) DEFAULT NULL COMMENT 'Client Type code will determine whether it is a sales customer ,purchase customer ,employee or store owner.',
  `active` varchar(45) NOT NULL DEFAULT 'Yes',
  PRIMARY KEY (`clientGuid`),
  UNIQUE KEY `UK_CLIENTMNILENUMBER` (`clientMobileNumber`),
  UNIQUE KEY `clientGuid_UNIQUE` (`clientGuid`),
  UNIQUE KEY `UK_CLIENTEMAILID` (`clientEmailID`),
  KEY `IX_CLIENTGUID` (`clientGuid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='THis Table is used to store the user details'$$

please can someone help me to insert these many rows using any scripts or any other easier way ?

See if this answer helps.

Amal
– Amal

2013-09-29 18:40:47 +00:00
Commented Sep 29, 2013 at 18:40 — Amal
– Amal, Commented Sep 29, 2013 at 18:40

Lóri Nóda · Accepted Answer · 2013-09-29 18:49:20Z

1

Try with the following link it will help you:

http://www.electrictoolbox.com/mysql-insert-multiple-records/

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

edited Sep 29, 2013 at 18:49

answered Sep 29, 2013 at 18:43

Lóri Nóda

7501 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wrikken Over a year ago

While it is OK to post a link on SO, it is encouraged to include the meat of the answer found under that link in your own answer, as the link may expire / disappear, which would make your answer useless if it didn't include the main points. See alse Provide context for links in the FAQ

Mihai · Accepted Answer · 2013-09-29 18:52:03Z

0

This would be the general idea of a php script

for($i=0;$i<count($numrows);$i++){

     $sql_query  = "INSERT INTO table (column1, column2 column3) VALUES ('$value1[$i]','$value2[$i]','$value3[$i]')"; 
}

If you can get the data into php

answered Sep 29, 2013 at 18:52

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Need any script/techniques for inserting multiple rows in mysql table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related