SQL statement: Import Excel data to a table

Example of OpenDataSource when accessing external data:

Note: SQL2000 enables the OpenDataSource function by default. However, sql2005 is disabled by default. If you want to use OpenDataSource in sql2005, you must first enable it.
Enabling method:
Method 1 (manually modify the SQL configuration): Select "enable OpenRowSet and OpenDataSource support" in "sql2005 Configuration tool"> "SQL Server peripheral application configurator"> "Impromptu remote query.
Method 2 (modify the configuration using SQL statements ):
-- Allow advanced configuration options
Exec sp_configure 'show advanced options', 1
Go
-- Reconfigure
Reconfigure
Go
-- Enable ad hoc distributed queries
Exec sp_configure 'ad hoc distributed queries ', 1
Go
-- Reconfigure
Reconfigure
Go

-- Use SQL statements to query Excel form data
Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = D: \ log.xls; user id = admin; Password =; extended properties = Excel 8.0 ')... [sheet1 $]

/*
Note:
Microsoft. Jet. oledb.4.0: Database provider, connection driver
Data source: Excel path
User ID: Excel user name. The default value is admin.
Password: password. Empty by default
Extended properties: Excel version
[Sheet1 $]: sheet1 form name
*/

-- Insert query results into other tables (note that the number of fields must be consistent and correspond to each other)
Insert into Table Name (Field 1, Field 2, Field 3, field 4)
Select personnel, time, content, result from OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = D: \ log.xls; user id = admin; Password =; extended properties = Excel 8.0 ')... [sheet1 $]