Easy way to do mysql "INSERT INTO" to Mysqllite

Question

Hi I have the following Mysql code (Which i export from Mysql DB before I delete my tables from it). Now I have a MySQLlite DB, and I have the corresponding tables in this database. But when I run the INSERT INTO command exported from MySQL database is not seems to be working in MySQLlite. When I research I see that I need to manually add each row as one single INSERT INTO command. I have 100 of rows. It's almost impossible to do that. Any Idea guys how to do this insertion? Thanks in advance..

MYSQL INSERT INTO Command:

INSERT INTO `DEPT` (`id`, `dept_name`, `bp_name`, `standard`, `created_by_id`, `modified_by_id`, `date_created`, `date_modified`) VALUES
(23,    'DEQP', 'Kaizen',   'IEC 60812',    286,    286,    '2016-05-16 14:32:30',  '2016-05-19 14:25:06'),
(31,    'DEQP', 'test', 'IEC 60812',    286,    286,    '2016-05-22 10:50:06',  '2016-05-22 10:55:55'),
(32,    'IT',   'Design',   'IEC 60812',    286,    286,    '2016-05-22 11:13:06',  '2016-05-22 11:13:06');

That syntax looks fine for SQLite. What happens when you run it? — duskwuff -inactive-, Jun 21 '16 at 02:27
[Worked fine for me.](http://pastebin.com/JASLVAYr) Are you sure the command you're running is the same as the one in your question? — duskwuff -inactive-, Jun 21 '16 at 03:10

Andrews B Anthony · Answer 1 · 2016-06-21T02:05:59.563

1

Export the data from table to a file using

  SELECT *
  INTO OUTFILE '/path/to/file.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM DEPT

And after exporting use the file to import data into your mysqlite server using

Load data local infile..... Check this link MySQL - LOAD DATA INFILE.

edited Jun 21 '16 at 02:05

answered Jun 21 '16 at 01:46

Andrews B Anthony

1,296
7
25

Andrews, I have already exported the data from Mysql, and the tables are already deleted from the Mysql Database. – vellattukudy Jun 21 '16 at 01:49
You have the query to insert 100 rows in mysql use the query to insert data into mysql and after that do the steps I've suggested – Andrews B Anthony Jun 21 '16 at 01:52
"use the query to insert data into mysql" that means I need to create the tables again in the database? Thats almost impossible also Andrews. Any other ways? – vellattukudy Jun 21 '16 at 02:02
yes you have to create tables again.... :( thats the sad part @vellattukudy – Andrews B Anthony Jun 21 '16 at 02:03

score 0 · Answer 2 · answered Jun 21 '16 at 04:18

0

SQLite insert one row per line.

SQLite with PHP_PDO will allow you insert multiple rows in one insert. Have a try SIDU: http://topnew.net/sidu see if it can help you.

answered Jun 21 '16 at 04:18

SIDU

2,168
1
10
19

Easy way to do mysql "INSERT INTO" to Mysqllite

2 Answers2