Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to export data from SQLServer to MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1 Export data from SQLServer

Execute 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 to be imported. (2) Export. This is done using the out command, followed by the name of the file to be exported. (3) use SQL statement to export. This action is done using the queryout command, which is similar to out, except that the data source is not the table or view name, but the SQL statement. (4) Export the format file. This action is done using the format command, followed by the format file name. Here are some common options:-f format_file format_file represents the format file name. This option depends on the above actions, if you are using in or out,format_file to represent an existing format file, and if you are using format to indicate the format file to be generated. The-x option is used in conjunction with-f format_file to generate files in xml format. -F first_row specifies which row of the exported table to export from, or which line of the imported file to import. -L last_row specifies the line to which the exported table ends, or when importing data from the imported file. -c uses the char type as the storage type, with no prefix and with "/ t" as the field separator and "/ n" as the line separator. -w is similar to-c, except that it is used when copying data using the Unicode character set and uses nchar as the storage type. -t field_term specifies the character separator, which defaults to "/ t". -r row_term specifies the line separator, which defaults to "/ n". -S server_name [/ instance_name] specifies the instance of the SQL Server server to which you want to connect. If this option is not specified, BCP connects to the default instance of SQL Server on this machine. If you want to connect to the default instance on a machine, you only need to specify the machine name. -U login_id specifies the user name to connect to the SQL Sever. -P password specifies the username and password to connect to SQL Server. -T specifies that BCP logs in to SQL Server using a trusted connection. If-T is not specified,-U and-P must be specified. -k specifies that empty columns are inserted with null values instead of the default values for this column.

2 Import data into MySQL and 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, MySQL will not insert the data until no one else reads the table.

2.2 Local if you specify the local keyword, the file is read from the client host. If local is not specified, the file must be located on the server.

The Replace & Ignore replace and ignore keywords control the duplicate 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 lines of existing lines with a unique key. If you do not specify any of the options, an error occurs when a duplicate key is found, and the rest of the text file is ignored.

The terminated by delimiter describes the delimiter of the field, which by default is the tab character (\ t) enclosed by describes the enclosing character of the field. The escape character of the escaped by description. By default, the backslash (backslash:\) lines keyword specifies that the delimiter for each record defaults to'\ n', which is the newline character.

2.5 Import partial columns load data infile can import files into the database according to the specified columns. When we want to import part of the data, we need to add some columns (columns / fields / field) to the MySQL database to meet 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 pathname is given, the server uses that pathname. (2) if a relative pathname with one or more front parts is given, the server searches the file relative to the data directory of the server. (3) if a file name without a front part is given, the server looks for the file in the database directory of the current database. For example: the file given by / myfile txt is read from the data directory of the server, while a file given as "myfile txt" is read from the database directory of the current database. 2.7 character set character set gbk specifies the character set as gbk, which is consistent with the file coding format (ANSI). Otherwise, Chinese data may become garbled after import.

2.8. flexible import allows you to insert some other columns while importing: load data local infile "F:/test.txt" into table table1 set pin='zs',create_date=current_timestamp

3 frequently asked questions

3.1 unable to run the Load Data command to execute load data localinfile Times error: The used command is not allowed with this MySQL version solution is to start the mysql client by adding-- local-infile=1 parameter $mysql-- local-infile=1-uroot-p123-h 127.0.0.1

The solution to export UTF-8 format with BCP is to add the-C option: $bcp "query sql …" Queryout "F:\ test.txt"-c-C65001-S1.2.3.4-Usa-P1111mysql Import data load data infile usage

After reading the above, do you have any further understanding of how to export data from SQLServer to MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.