MySQL - Bulk Insert

Asked By Somanathan E on 05-Jan-11 01:20 AM
How to insert more than 100 records using single insert statement in mYsql.
Reena Jain replied to Somanathan E on 05-Jan-11 01:26 AM
hi,

here is the query for you to insert multiple record with one query.

INSERT INTO beautiful (name, age)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),
  ('Samia', 22),
  ('Hui Ling', 25),
  ('Yumie', 29)";
Hope this will help  you
Somanathan E replied to Reena Jain on 05-Jan-11 01:32 AM
any other method except this.

Bulk Insert

Lalitha Kumaran replied to Somanathan E on 05-Jan-11 01:40 AM

USE YourDB
GO
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('First',1);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Second',2);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Third',3);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fourth',4);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fifth',5);
GO


USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

INSERT statements way: about 5 seconds
INSERT SELECT UNION ALL way: 40 miliseconds!!!


OR

Insert into <<table>>(col1,col2)
select col1,col2 from table1




DL M replied to Somanathan E on 05-Jan-11 02:24 AM
->You could simply use multiple INSERT statements, and could even submit them all at once, each terminated by a semicolon, like this:

 Input

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES('Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA');
INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES('M. Martian',
   '42 Galaxy Way',
   'New York',
   'NY',
   '11213',
   'USA');

Or, as long as the column names (and order) are identical in each INSERT, you could combine the statements as follows:

Input

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );
Reena Jain replied to Somanathan E on 05-Jan-11 04:48 AM
hi,

just if data is in any temp table then use this query

Insert Into table1 select * from temptable

but make sure both table having same table structure other wise use this query

insert into table1 (id, name) select id, name from temptable

hope this will help you
renu k replied to Somanathan E on 21-Jan-11 01:58 AM
insert into student
values('asha',14);

or
 
insert into student(name,age)
values('asha',14);

or

insert into student(&name,&age)
every time u evecute it will ask
Enter name
enter age