During project implementation, users often need to provide an Excel template, allow users to enter a basic data (such as department staff data, online exam question library, and device information) in EXCEL according to their actual conditions ), one reason is that you are familiar with Excel. The other reason is that you can directly import data from other data sources instead of entering data from the beginning.
After the user handed over the processed workbook to us, the general implementers will use the tools they are familiar with to import the Excel file into the system data table. I have been using tools to import it all the time, but today, a colleague told me that I could directly use the SQL Server Query analyzer to complete this function. I have never tried it before. So I tried it and found it really good and I don't need a tool, no need to configure, just a simple SQL statement. Next let's take a look at how to use SQL statements to import EXCEL to SQL Server. Step 1: place the prepared Excel file on the local machine. Note that the xls file name is in English, and the first line of XLS should be the field name. Step 2: Open the query analyzer and enter the following statement: -- enable ad hoc distributed queriesexec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'ad hoc distributed queries ', 1 reconfigure insert into rt_para (xlbh, msid, ywgd, DHZ) -- database table name, field name select xlbh, msid, ywgd, DHZ -- field name in Excel from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 5.0; HDR = yes; database = D: \ yewei.xls', sheet1 $) -- note that XLS should be written to the physical location -- disable ad hoc distributed queriesexec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure Step 3: Execute the statement to complete the import operation. How are you doing?
