|
There are many tools can be used to migrate data into mysql database. One of the simplest tool is the command line of mysql.
It's quick and easy to load text data into mysql from text file, which can be easily exported from sql server by DTS. However, the real data migration is never so straight forward. We will need some time fiddling and testing.
Below is the example of import text file into a table.
-- create a test file, c:/mysql/tmp/testnews.txt:
1|First News||2008-12-07 11:08:12
2|Second News| There is no news|2008-12-08 10:08:12
3|Welcome mysql|Mysql is a very good database, as good as others.|2008-12-08 11:08:12
-- Create table and load the text file
mysql> create table test_news
-> (
-> news_id int,
-> title varchar(200),
-> news varchar(20000),
-> news_date datetime
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> desc test_news;
+-----------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------+------+-----+---------+-------+
| news_id | int(11) | YES | | NULL | |
| title | varchar(200) | YES | | NULL | |
| news | varchar(20000) | YES | | NULL | |
| news_date | datetime | YES | | NULL | |
+-----------+----------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> LOAD DATA LOCAL INFILE 'c:/mysql/tmp/testnews.txt'
-> into table test_news
-> FIELDS TERMINATED BY '|'
-> LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test_news;
+---------+---------------+---------------------------------------------------+---------------------+
| news_id | title | news | news_date |
+---------+---------------+---------------------------------------------------+---------------------+
| 1 | First News | | 2008-12-07 11:08:12 |
| 2 | Second News | There is no news | 2008-12-08 10:08:12 |
| 3 | Welcome mysql | Mysql is a very good database, as good as others. | 2008-12-08 11:08:12 |
+---------+---------------+---------------------------------------------------+---------------------+
3 rows in set (0.00 sec) |
|