SQL Server export data to MySQL instance introduction _mssql

1 exporting data from SQL Server
Perform bcp:
BCP "..." queryout "F:\test.txt"-c–s1.2.3.4-usa-p1111
Command parameter meaning:
(1) Import.
This action is done using the in command followed by the name of the file you want to import.
(2) export.
This action is done using the Out command followed by the name of the file you want to export.
(3) Export using SQL statements.
This action is done using the Queryout command, which is similar to out, except that the data source is not a table or view name, but rather an SQL statement.
(4) Export the format file.
This action is done using the Format command, followed by the file name.
The following are some common options:
-F Format_file
Format_file represents the format file name. This option depends on the action described above, if you are using in or Out,format_file to indicate a format file that already exists, and if you are using format, it means the file you want to generate.
-X
This option is used in conjunction with-F format_file to generate XML-formatted format files.
-F First_row
Specifies which row from the exported table is exported, or from which line of the file being imported.
-L Last_row
Specifies which line to end when the exported table is to be directed to, or from the file being imported.
-C
Use the char type as the storage type, with no prefix and "/t" as the field delimiter, and "/n" as the row separator.
-W
Similar to-C, only used when copying data using the Unicode character set, and nchar as the storage type.
-T Field_term
Specifies a character delimiter, which is "/T" by default.
-R Row_term
Specifies a line delimiter, which defaults to "/n".
-S server_name[/instance_name]
Specifies an instance of the SQL Server server to which you want to connect, and if this option is not specified, bcp connects to the native SQL Server default instance. If you want to connect to a default instance on a machine, simply specify the machine name.
-U login_id
Specifies the user name to connect to SQL Sever.
-p password
Specifies the user name password to connect to SQL Server.
-T
Specifies that bcp log on to SQL Server using a trusted connection. If-T is not specified, you must specify-U and-P.
-K
Specifies that empty columns are inserted using null values instead of the default values for this column.
2 importing data to MySQL
Execute the Load Data command:
Load data local infile "f:/test.txt" into table Table1character set GBK;
Command parameter meaning:
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 a keyword low_priority, mysql will wait until no one else reads the table to insert the data.
2.2 Local
If you specify a local keyword, the file is read from the client host. If local is not specified, the file must be located on the server.
2.3 Replace & Ignore
The Replace and Ignore keywords control the repetitive processing of existing unique key records. If you specify replace, the new line replaces the existing row with the same unique key value. If you specify ignore, skip the input of duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored.
2.4 Separator
Terminated by describes the separator for the field, which is the tab character by default (\ t)
Enclosed by describes the enclosing character of the field.
Escaped by description of the escape character. The default is a backslash (backslash:\)
The lines keyword specifies the separation of each record Fummer that ' \ n ' is the newline character
2.5 Import Partial columns
The load data infile can import files into the database according to the specified columns. When we want to import a portion of the data, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. For example, when we are upgrading from an Access database to a MySQL database
The following example shows how to import data into a specified column (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 given, the server uses the path name.
(2) If a relative pathname with one or more predecessor parts is given, the server searches for the file relative to the server's data directory.
(3) If a filename is given without a predecessor, the server looks for files in the database directory of the current database.
For example:/MyFile txt "The file given is read from the server's data directory, and a file as" MyFile txt "is read from the database directory of the current database.
2.7 Character Set
The character set GBK specifies that the character set is GBK, consistent with the encoding format (ANSI) of the file. Otherwise, Chinese data may become garbled after import.
2.8 Flexible Import
You can insert some other columns while importing:
Load data local infile "f:/test.txt" into table table1 set pin= ' Zs ', create_date=current_timestamp;
3 FAQ
3.1 Unable to run the Load Data command
Execute load Data localinfile times error:
The used command isn't allowed with this MySQL version
The workaround is: Start the MySQL client with the--local-infile=1 parameter
$ MySQL--local-infile=1–uroot–p123–h 127.0.0.1
3.2 Export UTF-8 format with bcp
The workaround 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 infile usage