SQL Server task export Excle

-- SQL statement: Use the following stored procedure * -- export data in Excel to export the queried data to Excel, which contains the field name. The file is a real Excel file. If the file does not exist, the file is automatically created, if the table does not exist, the automatic creation of the table is based on the general considerations. Only the use of standard data types can be exported: directly copy and execute the creation and storage process --

-- SQL statement: Use the following stored procedure/* -- export data in Excel to export the queried data to Excel, which contains the field name. The file is a real Excel file. If the file does not exist, the file will be automatically created. If the table does not exist, the table will be automatically created based on the general considerations. Only standard data types can be exported. Use method: directly copy and execute the creation and storage process --

-- The SQL statement uses the following stored procedure

/* -- Data export Excel

Export the queried data to Excel, including the field name. The file is a real Excel file.
If the file does not exist, the file is automatically created.
If the table does not exist, the table is automatically created.
Only standard data types can be exported for the sake of universality.

Usage:

Directly copy and execute the storage creation process
-- Chen runcheng 2014.04 --*/

/* -- Call example

P_exporttb @ sqlstr = 'select * from Table name', @ path = 'C: \ ',@fname='aa.xls', @ sheetname = 'sheet1'
--*/
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_exporttb] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [p_exporttb]
GO
Create proc p_exporttb
@ Sqlstr sysname, -- query statement. If order by is used in the query statement, add top 100 percent.
@ Path nvarchar (1000), -- file storage directory
@ Fname nvarchar (250), -- file name
@ Sheetname varchar (250) = ''-- Name of the worksheet to be created. The default value is the file name.
As
Declare @ err int, @ src nvarchar (255), @ desc nvarchar (255), @ out int
Declare @ obj int, @ constr nvarchar (1000), @ SQL varchar (8000), @ fdlist varchar (8000)

-- Parameter detection
If isnull (@ fname, '') = ''set @fname='temp.xls'
If isnull (@ sheetname, '') ='' set @ sheetname = replace (@ fname ,'.','#')

-- Check whether the file already exists
If right (@ path, 1) <> '\' set @ path = @ path + '\'
Create table # tb (a bit, B bit, c bit)
Set @ SQL = @ path + @ fname
Insert into # tb exec master .. xp_fileexist @ SQL

-- Database creation statement
Set @ SQL = @ path + @ fname
If exists (select 1 from # tb where a = 1)
Set @ constr = 'driver = {Microsoft Excel DRIVER (*. xls)}; DSN = '''; READONLY = false'
+ '; CREATE_DB = "' + @ SQL + '"; DBQ =' + @ SQL
Else
Set @ constr = 'provider = Microsoft. Jet. OLEDB.4.0; Extended Properties = "Excel 5.0; HDR = YES'
+ '; DATABASE =' + @ SQL + '"'

-- Connect to the database
Exec @ err = sp_oacreate 'ADODB. connection', @ obj out
If @ err <> 0 goto lberr

Exec @ err = sp_oamethod @ obj, 'open', null, @ constr
If @ err <> 0 goto lberr

-- SQL statement used to create a table
Declare @ tbname sysname
Set @ tbname = '# tmp _' + convert (varchar (38), newid ())
Set @ SQL = 'select * into ['+ @ tbname +'] from ('+ @ sqlstr +')'
Exec (@ SQL)

Select @ SQL = '', @ fdlist =''
Select @ fdlist = @ fdlist + ',' + a. name
, @ SQL = @ SQL + ', [' + a. name + ']'
+ Case when B. name in ('Char ', 'nchar', 'varchar ', 'nvarchar') then
'Text ('+ cast (case when a. length> 255 then 255 else a. length end as varchar) + ')'
When B. name in ('tynyint', 'int', 'bigint', 'tinyint') then 'int'
When B. name in ('smalldatetime', 'datetime') then 'datetime'
When B. name in ('money', 'smallmoney') then 'money'
Else B. name end
FROM tempdb .. syscolumns a left join tempdb .. policypes B on a. xtype = B. xusertype
Where B. name not in ('image', 'text', 'uniqueidentifier', 'SQL _ variant', 'ntext', 'varbinary ', 'binary', 'timestamp ')
And a. id = (select id from tempdb .. sysobjects where name = @ tbname)
Select @ SQL = 'create table ['+ @ sheetname
+ '] (' + Substring (@ SQL, 2,8000) + ')'
, @ Fdlist = substring (@ fdlist, 2,8000)

Exec @ err = sp_oamethod @ obj, 'execute ', @ out, @ SQL
If @ err <> 0 goto lberr

Exec @ err = sp_oadestroy @ obj

-- Import data
Set @ SQL = 'openrowset (''microsoft. JET. OLEDB.4.0 '', ''excel 5.0; HDR = YES
; DATABASE = '+ @ path + @ fname + ''', [' + @ sheetname + '$])'

Exec ('insert into '+ @ SQL +' ('+ @ fdlist +') select' + @ fdlist + 'from [' + @ tbname + ']')

Set @ SQL = 'drop table ['+ @ tbname +']'
Exec (@ SQL)
Return

Lberr:
Exec sp_oageterrorinfo 0, @ src out, @ desc out
Lbexit:
Select cast (@ err as varbinary (4) as error code
, @ Src as error source, @ desc as error description
Select @ SQL, @ constr, @ fdlist