Developers, Developers, Developers! Maksim Sorokin IT Blog

22Jul/10Off

Other Way to Import Data to MySQL

What to do, if SQL file for some reason has an error in the middle and it is big enough to have problems opening it with text editor. If you can live without that small piece of data, which causes you problems, then here a way to import all valid data from SQL file to the database.

Imagine, you have a table:

mysql> describe test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| val   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

And you have following SQL script with an error in the middle:

insert into test_table (val) values (1);
insert into test_table (val) values (2a);
insert into test_table (val) values (3);

Traditionally, you would use the following way to import data:

mah@mah-laptop:~/Desktop$ mysql -u username db -p < data.sql
Enter password:
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'

And in this case, test_table would contain only the data up to the failing line:

mysql> select * from test_table;
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

Another way to import data is through MySQL source command:

mysql> source /home/mah/Desktop/datasql
Query OK, 1 row affected (0.00 sec)

ERROR 1054 (42S22): Unknown column '2a' in 'field list'
Query OK, 1 row affected (0.00 sec)

And in this case all valid statements will be executed:

mysql> select * from test_table;
+------+
| val  |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)
Tagged as: , , , Comments Off
Comments (0) Trackbacks (0)

Sorry, the comment form is closed at this time.

Trackbacks are disabled.