Two ways to import data from an Excel table into a SQL database _mssql

Originally in the research of micro-trust public platform, the eldest brother temporarily hand me a task, the Excel table data into the SQL database, I think this so easy. I didn't want to spend two days on it.

Introduction: A table with the following structure in the database (table a)

I only take the key and the column has a lot of data.

There is an Excel file (table B) with ID and name control, and N-structure-like table (c table) (no ID that column)

My task in B table found in the C table of the corresponding ID, and then add ID to C table, and finally, according to the ID is unique, with C table data to update a table data. This mainly involves the transformation between Excel tables and database tables.

Solution One: Use Excel+mysql for Excel to handle

is MySQL for Excel is a direct operation of Excel MySQL database plug-ins, you can modify the database like the Excel table, of course, you can also do table merging and so on. Plug-in installation, everyone Baidu Bar.

A problem was encountered when using this, MySQL for Excel can connect to the local database, but the database on the server is always not connected .... Programme one bankrupt

Scenario Two: Import the Excel table directly into the database and use the SQL statement to do the operation.

Notice here that 2.1 is the phone number in the Excel form to be formatted as text, and the second is to unify the traditional characters. When the space is garbled, remove the space to rewrite the input.