SQL statement import/export Excel (reproduced from: White so blog)

 

Import / Export and generate EXCEL
-- To import data from an Excel file to a SQL database, you can simply use the following statement:
/**/ /**/ /**/ /*========================================================== ======================================*/
-- If the table to be imported already exists
Insert   Into Table Select   *   From  
OpenRowSet ( ' Microsoft. Jet. oledb.4.0 '
, ' Excel 5.0; HDR = yes; database = c: \ test.xls ' , Sheet1 $)

-- If you import data and generate a table
Select   *   Into Table From  
OpenRowSet ( ' Microsoft. Jet. oledb.4.0 '
, ' Excel 5.0; 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 header based on the data to be received, you can simply use it:
Insert   Into   OpenRowSet ( ' Microsoft. Jet. oledb.4.0 '
, ' Excel 5.0; HDR = yes; database = c: \ test.xls ' , Sheet1 $)
Select   *   From Table


-- If the Excel file does not exist, you can use bcp to convert it into an Excel-like file. Note the case sensitivity:
-- Export tables
Exec Master .. xp_mongoshell ' BCP database name. DBO. Table name out "C: \ test.xls"-C-s "server name"-U "username"-P "password" '

-- Export Query Information
Exec Master .. xp_mongoshell ' 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" '

/**/ /**/ /**/ /*-- Note:
C: \ test.xls is the Excel file name for import/export.
Sheet1 $ is the worksheet name of the Excel file. You must add $ to use it normally.
--*/