-- 目標表已存在
INSERT INTO 目標表
SELECT * FROM OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=C:\test.xls',
'SELECT * FROM [Sheet1$]'
);
-- 導入並自動建表
SELECT * INTO 目標表
FROM OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=C:\test.xls',
'SELECT * FROM [Sheet1$]'
);
注意: HDR=YES 表示首行為列名;工作表名需加 $ 並用方括號包裹;64 位環境需安裝 Microsoft.ACE.OLEDB.12.0 驅動並啟用 Ad Hoc Distributed Queries。
INSERT INTO OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=C:\test.xls',
'SELECT * FROM [Sheet1$]'
)
SELECT * FROM 來源表;
-- 導出表
EXEC master..xp_cmdshell 'bcp 數據庫.dbo.表名 out "C:\test.xls" /c /S"服務器" /U"用戶" -P"密碼"'
-- 導出查詢結果
EXEC master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 數據庫.dbo.表名 ORDER BY col1" queryout "C:\test.xls" /c /S"服務器" /U"用戶" -P"密碼"'
缺點: 輸出實為文本文件 (制表符分隔),非真正 .xls 格式,無法直接雙擊用 Excel 正常打開複雜格式。
CREATE PROCEDURE p_exporttb
@tbname sysname,
@path nvarchar(1000),
@fname nvarchar(250) = ''
AS
BEGIN
DECLARE @obj int, @hr int, @constr nvarchar(1000), @sql varchar(8000), @fdlist varchar(8000)
IF ISNULL(@fname,'') = '' SET @fname = @tbname + '.xls'
IF RIGHT(@path,1) <> '\' SET @path = @path + '\'
-- 構建連接字符串 (Excel Driver)
SET @constr = 'DRIVER={Microsoft Excel Driver (*.xls)};DSN='';READONLY=FALSE;CREATE_DB="' + @path + @fname + '";DBQ=' + @path + @fname
-- 創建 ADODB.Connection
EXEC @hr = sp_OACreate 'ADODB.Connection', @obj OUT
IF @hr <> 0 GOTO LBERR
EXEC @hr = sp_OAMethod @obj, 'Open', NULL, @constr
IF @hr <> 0 GOTO LBERR
-- 可選:覆蓋模式先刪表
-- EXEC @hr = sp_OAMethod @obj, 'Execute', @out OUT, 'DROP TABLE [' + @tbname + ']'
-- 動態構建 CREATE TABLE 語句 (類型映射)
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 ('tinyint','int','bigint') THEN 'INT'
WHEN b.name IN ('smalldatetime','datetime') THEN 'DATETIME'
WHEN b.name IN ('money','smallmoney') THEN 'MONEY'
ELSE b.name END
FROM syscolumns a LEFT JOIN systypes b ON a.xtype = b.xusertype
WHERE b.name NOT IN ('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
AND OBJECT_ID(@tbname) = a.id
SET @sql = 'CREATE TABLE [' + @tbname + '](' + SUBSTRING(@sql,2,8000) + ')'
SET @fdlist = SUBSTRING(@fdlist,2,8000)
EXEC @hr = sp_OAMethod @obj, 'Execute', @out OUT, @sql
IF @hr <> 0 GOTO LBERR
-- 寫入數據
SET @sql = 'OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'', ''Excel 5.0;HDR=YES;DATABASE=' + @path + @fname + ''', [' + @tbname + '$])'
EXEC('INSERT INTO ' + @sql + '(' + @fdlist + ') SELECT ' + @fdlist + ' FROM ' + @tbname)
EXEC @hr = sp_OADestroy @obj
RETURN
LBERR:
EXEC sp_OAGetErrorInfo 0, @src OUT, @desc OUT
SELECT CAST(@hr AS varbinary(4)) AS 錯誤號, @src AS 錯誤源, @desc AS 錯誤描述
END
調用: EXEC p_exporttb @tbname='地區資料', @path='C:\', @fname='aa.xls'
OPENROWSET('Microsoft.ACE.OLEDB.12.0', ...) 支持 .xlsx (Office 2007+)。EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;TEXT(255) 可能截斷。xp_cmdshell (安全風險),改用 SQL Agent 代理作業調用 BCP/SSIS。