T-SQL usage 4: OpenDataSource, OpenRowSet

OpenDataSource:

Provide special connection information without using the name of the linked server and use it as part of the four-part object name.

Syntax

OpenDataSource (provider_name, init_string)

Parameters

Provider_name

Register as the name of the progid of the ole db access interface used to access the data source.Provider_nameThe data type of isChar, No default value.

Init_string

Connection string to be passed to the targetProgramOfIdatainitializeInterface. The string syntax of the provider is based on key value pairs. These key value pairs are separated by semicolons. For example:" Keyword1=Value; Keyword2=Value ".
For example, read an Excel table to the database.
Use news
Select * into [data] From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; database = absolute path')... [Excel table name $]

Note that the default HDR value is yes, that is, the first row in the Excel table can be set to no as the column name.
Select * into [data] From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = no; database = absolute path')... [Excel table name $]

write database data to an Excel table
use news
insert into OpenDataSource ( 'Microsoft. jet. oledb.4.0 ' , 'excel 8.0; database = absolute path' ' )... [ Excel table name $ ] select * from table name in the database

OpenRowSet is consistent with the above functions, except that it can be queried, such:
Use OpenRowSet (note the differences between different query results when HDR and IMEX are set)
1. HDR = no; IMEX = 0;
Select *
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = no; IMEX = 0; database = c: \ test.xls', worksheetname $)

2. HDR = no; IMEX = 1;
Select *
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = no; IMEX = 1; database = c: \ test.xls', worksheetname $)

3. HDR = yes; IMEX = 0;
Select *
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = yes; IMEX = 0; database = c: \ test.xls', worksheetname $)

4. HDR = yes; IMEX = 1;
Select *
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = yes; IMEX = 1; database = c: \ test.xls', worksheetname $)