Import Excel data into a SQL Server database

Method One:

See the operation step directly

Right-click on the database and select "Tasks", select "Import data" and we'll see a popup pop-up Data dialog box

The fields above Excel are best named for the same fields that you want to import into the final table. Suppose the final table is a table. Assemble well such as:

Follow the steps and you will eventually generate a new table (the temporary table B table).

We can use the tool that the MSSQL table data is exported as an INSERT statement tool, which is to generate an INSERT INTO statement for the data that is queried.

Finally, executing the INSERT INTO statement in Table A allows you to eventually put the data in Excel into the database.

Method Two:

Import directly from a statement

Insert into Hr_staff (Uuid,dept,staff_name,gender,nation,birthday,political,native_city,identy,
Position,pos_level,work_date,pos_begin,gradschool,professional,degree,education,remark)
SELECT * from OpenRowSet (' microsoft.ace.oledb.12.0 ', ' Excel 8.0; Hdr=yes;imex=1;database=d:\13.xls ', ' select * from [sheet1$] ')

Look at Excel. fields on Excel are consistent with the fields in insert into above

With this statement you will encounter a lot of problems, we follow the prompt to solve the problem.

Question one:

The OLE DB provider ' Microsoft.Jet.OLEDB.4.0 ' is configured to run in single-threaded apartment mode, so the provider cannot be used for distributed query resolution: A.the database executing the above SQL statement must be a local database, and the above error will be reported if it is a remote database. B.The jet.oledb.4.0 driver is not available in the 64SQL engine.,Workaround: Download a ace.oledb.12.0 for X64 bit driver and change the connection string microsoft.jet.oledb.4.0 to microsoft.ace.oledb.12.0question two:Unable to create an instance of the OLE DB provider "microsoft.ace.oledb.12.0" for the linked server "(null)". Workaround:--turn on the import feature
    execsp_configure'Show advanced Options',1
    Reconfigure
    execsp_configure'Ad Hoc Distributed Queries',1
    Reconfigure
    --allow ace.oledb.12 to be used in the process
    EXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0', N'allowinprocess', 1
    --Allow dynamic Parameters
    EXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0', N'dynamicparameters', 1

Import Excel data into a SQL Server database