Import EXCEL Data to the SQL SERVER database.

Import EXCEL Data to the SQL SERVER database.

Method 1 ..

View the procedure

Right-click the database, select "task", and select "import data". The pop-up dialog box for "Fade in data" appears.

It is best to name the fields in Excel the same as those in the table to be imported. Assume that the final table is table. Assembled as follows:

Follow these steps to generate a new table (temporary table B ).

You can use the tools to export MSSQL table data to Insert statements: Generate insert into statements for all the queried data.

Finally, execute the insert into statement in Table A to put the data in excel into the database.

Method 2:

Direct import using statements

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 $] ')

The excel Field should be consistent with the insert into field.

If you use this statement, you will encounter a lot of problems. We will follow the prompts to Solve the Problem step by step.

Question 1:

Ole db access interface 'Microsoft. jet. OLEDB.4.0 'is configured to run in single-threaded unit mode. Therefore, this access interface cannot be used for Distributed Query. solution:. the database that executes the preceding SQL statement must be a local database. If it is a remote database, the preceding error is reported. B. jet is not provided in 64 SQL Engine. oledb.4.0 driver. Solution: download an ACE. oledb.12.0 for X64 driver, and connect the connection string to Microsoft. jet. oledb.4.0 changed to Microsoft. ACE. OLEDB.12.0 problem 2: The ole db access interface "Microsoft. ace. OLEDB.12.0. Solution: -- enable the import function
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad Hoc Distributed Queries ', 1
Reconfigure
-- ACE. OLEDB.12 is allowed in the process.
EXEC master. dbo. sp_MSset_oledb_prop N 'Microsoft. ACE. OLEDB.12.0 ', n' allowinprocess', 1
-- Allow dynamic parameters
EXEC master. dbo. sp_MSset_oledb_prop N 'Microsoft. ACE. OLEDB.12.0 ', n' DynamicParameters', 1