1. Export data from SQLServer
Run BCP:
Bcp "..." queryout "F: \ test.txt"-c-S1.2.3.4-Usa-P1111
Command parameter description:
(1) import.
This operation is completed using the in command, followed by the file name to be imported.
(2) Export.
This operation is completed using the out command, followed by the file name to be exported.
(3) use SQL statements to export data.
This operation is completed using the queryout command. It is similar to out, but the data source is not the table or view name, but an SQL statement.
(4) export the format file.
This operation is completed using the format command, followed by the format file name.
The following describes some common options:
-F format_file
Format_file indicates the format file name. This option depends on the preceding action. If in or out is used, format_file indicates an existing format file. If format is used, it indicates a format file to be generated.
-X
This option must be used with-f format_file to generate an xml format file.
-F first_row
Specifies the row of the exported table or the row of the imported file.
-L last_row
Specifies the end of the exported table or the end of the imported data.
-C
Char is used as the storage type, with no prefix and "/t" as the field delimiter, and "/n" as the row delimiter.
-W
Similar to-c, it is used only when Unicode character sets are used to copy data, and nchar is used as the storage type.
-T field_term
Specifies the delimiter. The default Delimiter is "/t ".
-R row_term
Specifies the line delimiter. The default value is "/n ".
-S server_name [/instance_name]
Specifies the instance of the SQL Server to be connected. If this option is not specified, BCP connects to the default instance of the local SQL Server. To connect to the default instance on a machine, you only need to specify the machine name.
-U login_id
Specifies the user name used to connect to the SQL server.
-P password
Specify the username and password used to connect to SQL Server.
-T
Specify BCP to log on to SQL Server using a trusted connection. -U and-P must be specified if-T is not specified.
-K
The null value is used to insert an empty column, rather than the default value of this column.
2. import data to MySQL
Run the Load Data command:
Load data local infile "F:/test.txt" into table table1character set gbk;
Command parameter description:
Load data [low_priority] [local] infile 'file _ name txt '[replace | ignore]
Into table tbl_name
[Fields
[Terminated by 'T']
[OPTIONALLY] enclosed by '']
[Escaped by '\']
[Lines terminated by 'n']
[Ignore number lines]
[(Col_name,)]
2.1 Low_priority
If you specify the keyword low_priority, MySQL inserts data only when no one else reads the table.
2.2 Local
If the local keyword is specified, the file is read from the client host. If local is not specified, the file must be on the server.
2.3 Replace & Ignore
The replace and ignore keywords control repeated processing of existing unique key records. If you specify replace, the new row replaces the existing row with the same unique key value. If you specify ignore, skip the input of duplicate rows of existing rows with a unique key. If you do not specify any option, an error occurs when the duplicate key is found, and the remaining part of the text file is ignored.
2.4 Separator
Terminated by delimiter used to describe the field. It is a tab character (\ t) by default)
Enclosed by describes the starting characters of a field.
Escape characters described by escaped. The default value is the backslash (backslash :\)
The lines keyword specifies that the delimiter of each record is '\ n' by default, that is, the line break.
2.5 import some columns
Load data infile can import files to the database according to the specified columns. When we want to import part of the data, we need to add some columns (columns/fields) to the MySQL database to meet some additional needs. For example, when we want to upgrade from the Access database to the MySQL database
The following example shows how to import data to a specified field:
Load data infile "/home/Ordertxt" into table Orders (Order_Number, Order_Date, Customer_ID );
2.6 relative & absolute path
(1) If an absolute path name is provided, the server uses this path name.
(2) If a relative path name of one or more front parts is given, the server searches for files in the data directory of the server relative to the server.
(3) If a file name without a front-end component is provided, the server searches for a file in the database directory of the current database.
For example, the file in/myfile txt is read from the server's data directory, while the file in myfile txt is read from the database directory of the current database.
2.7 Character Set
Character set gbk specifies that the character set is gbk, which is consistent with the file encoding format (ANSI. Otherwise, Chinese data may become garbled after being imported.
2.8 Flexible import
You can insert some other columns while importing them:
Load data local infile "F:/test.txt" into table table1 set pin = 'zs', create_date = current_timestamp;
3. FAQs
3.1 The Load Data command cannot be run
When you execute load data localinfile, an error is returned:
The used command is not allowed with this MySQL version
The solution is to add the -- local-infile = 1 parameter to start the mysql client.
$ Mysql -- local-infile = 1-uroot-p123-h 127.0.0.1
3.2 export UTF-8 format with BCP
The solution is to add the-C option:
$ Bcp "query SQL... "Queryout" F: \ test.txt "-c-C65001-S1.2.3.4-Usa-P1111
Mysql import data load data infile usage
