How SQL Server Imports Excel

--From the Excel file, import data into the SQL database, very easy, directly with the following statement:/*=================================================================== */--assumes that the table that accepts the data import already exists insert into table select * from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\test.xls ', sheet1$)--assuming import data and generate table select * into table from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\test.xls ', sheet1$)/*===================================================================*/- -Assuming that you are exporting data from a SQL database to Excel, assuming that the Excel file already exists and that you have created a header for the data you want to receive, you can simply use: INSERT INTO OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\test.xls ', sheet1$) SELECT * FROM table-assuming that the Excel file does not exist, you can also use BCP to lead to Excel-like files, note uppercase and lowercase:--case of export table exec Master.. xp_cmdshell ' bcp database name. dbo. Table name out "C:\Test.xls"/C-/s "server name"/u "username"-P "password" '--case of export query exec master. xp_cmdshell ' bcp ' select au_fname, au_lname from pubs. Authors ORDER BY au_lname "Queryout" C:\Test.xls "/C-/s" server name "/u" username "-P" password "'/*--Description: C:\Test.xls for import/export EX CEL file name. sheet1$ is the worksheet name for the Excel file, and you typically add$ ability to use normally.--*/ 

How SQL Server Imports Excel