Export SQL query Results to excel

In peacetime work often encounter, SQL query data need to send to the business people, each time you manually execute the script and then copy the data into Excel, it is time consuming, can consider automatically execute the query and send the result message.

Two-Step implementation:

1. Execute query to save the result as Excel, use stored procedure here

1 Create procedure [dbo].[Pr_dept_bak]2 3  as4 5 begin6 7 ---You can increase the query condition or more data processing of the data table;8 9 ---Put the result in a new data table and then export the new table to an Excel file;Ten  One Declare @file_path varchar( $);--the path to the exported Excel file; A  - Declare @file_name varchar( $);--Export the file name of Excel; -  the Declare @exec_sql  varchar( $);--SQL statements; -  - ---defined separately to make it easier to modify the path or file name later.  -  + Set @file_path = 'E:\Dept_Bak\' -  + Set @file_name = 'Dept' + CONVERT(varchar( -),GETDATE(), the)+'. xls' A  at Set @exec_sql = 'SELECT * from Book.dbo.users_dept'  ---The full path used by the data table; -  - Set @exec_sql = 'bcp "'+@exec_sql+'"Queryout"'+@file_path+"'+@file_name+'"-c-t-u" sa "-P" SQLPassword "'; -  - ----u "sa"-P "SQLPassword" This is the SA account and password of the database; -  in execMaster.. xp_cmdshell@exec_sql -  to End

View Code

Export SQL query Results to excel