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.
--*/