excel数据导入（Excel data import）.docVIP

excel数据导入（Excel data import） SQL import / export Excel This is something on the CSDN Zou key, recommended to everyone. From the Excel file, importing data into the SQL database is simple, using the following statement directly: /*===================================================================*/ -- if you accept data, the import table already exists Insert into table select * from OPENROWSET (MICROSOFT.JET.OLEDB.4.0? Excel 5; HDR=YES; DATABASE=c:\test.xls, sheet1$) -- if you import data and generate tables Select * into table from OPENROWSET (MICROSOFT.JET.OLEDB.4.0? Excel 5; HDR=YES; DATABASE=c:\test.xls, sheet1$) /*===================================================================*/ - - if you export data from the SQL database to Excel, if the Excel file already exists and you have created a good header in the data you want to receive, you can simply use it: Insert into OPENROWSET (MICROSOFT.JET.OLEDB.4.0? Excel 5; HDR=YES; DATABASE=c:\test.xls, sheet1$) Select * from table - if the Excel file does not exist, you can also use BCP to guide the class Excel file. Note the case: -- exporting tables EXEC, master..Xp_cmdshell, BCP, database name,.Dbo., table name, out, c:\test.xls, /c, -/S, server name, /U, user name, -P, password -- exporting queries EXEC, master..Xp_cmdshell, BCP, SELECT, au_fname, au_lname, FROM, ORDER, BY, au_lname, queryout, c:\test.xls, /c, -/S, server name, /U, user name, -P, password, pubs..Authors Explain. C:\test.xls is the import / export Excel file name Sheet1$is the worksheet name of the Excel file, typically $plus for normal use Here is a way to export a real Excel file: /*-- EXCEL export data Export the data from the table to Excel, which contains the field name and the file is a real Excel file If the file does not exist, the file will be created automatically If the table does not exist, the table will be created automatically Based on versatility considerations, only standard data types are supported ---*/ /*-- call example P_expor