SQL Server export data to MySQL example introduction

1 exporting data from SQL Server


Execution 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) exported.


This action is done using the Out command, followed by the name of the file you want to export.


(3) is exported 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 when the data is being imported from the file.


-C


uses 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 defaults to "/t".


-R row_term


Specifies the 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 local 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 Import data to MySQL


Execute 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 the keyword low_priority, then MySQL will wait until no one else reads the table before inserting 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


Replace and Ignore keywords control duplicate 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 delimiter of the field by default, which is the tab character (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 a 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 files relative to the server's data directory.


(3) If you give a filename without a predecessor, the server looks for files in the database directory of the current database.


Example:/MyFile txt "The file given is read from the server's data directory, and a file given as" 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, consistent with the encoding format (ANSI) of the file. Otherwise, Chinese data may become garbled after import.


2.8 Flexible Import


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 cannot run the load data command


Execute load data localinfile times wrong:


The used command isn't allowed with this MySQL version

The
workaround is to 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