附:Openrowset OpenDataSoure 以及xp_cmdshell 打开的代码:
SP_CONFIGURE ‘show advanced options’,1
GO
RECONFIGURE
GO
SP_CONFIGURE ‘Ad Hoc Distributed Queries’,1
GO
RECONFIGURE
GO
SP_CONFIGURE ‘xp_cmdshell’,1
GO
RECONFIGURE
GO
SP_CONFIGURE ‘show advanced options’,0
GO
RECONFIGURE
GO
USE [testdemo]
GO
/****** Object: StoredProcedure [dbo].[Sp_InputExceltoSQL] Script Date: 12/19/2016 09:53:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Sp_InputExceltoSQL]
@dir NVARCHAR(100),--EXCEL文件存放路径:
@tabname NVARCHAR(50) --定义导入到数据库中的表名
AS
SET NOCOUNT ON
CREATE TABLE #t([filename] NVARCHAR(1000))
IF RIGHT(@dir,1)<>'\'
SET @dir=@dir+'\'
DECLARE @cmd NVARCHAR(1000)
SET @cmd = N'dir "' + @dir + '*.xls*" /B'
INSERT #t EXEC master..xp_cmdshell @cmd
DELETE #t WHERE [filename] IS NULL
--在SQL中创建表:
DECLARE @S nvarchar(MAX)
IF OBJECT_ID(@tabname) IS NULL
BEGIN
SELECT TOP 1 @S='SELECT TOP 0 * INTO '+ @tabname+' FROM OPENROWSET(
''MICROSOFT.ACE.OLEDB.12.0'', ''EXCEL 12.0;HDR=YES;IMEX=2;DATABASE='+@DIR+[filename]+'''
,''SELECT * FROM [Sheet1$]'')'
FROM #t
EXEC(@S)
END
--开始导入目录下的文件
SET @S = ''
SELECT @S = @S + 'INSERT '+@tabname+' SELECT * FROM OPENROWSET(
''MICROSOFT.ACE.OLEDB.12.0'', ''EXCEL 12.0;HDR=YES;IMEX=2;DATABASE='+@DIR+FILENAME+'''
,''SELECT * FROM [Sheet1$]'')'
FROM #t
EXEC(@S)
SET NOCOUNT OFF
调用方式(路径,数据库表名)
exec dbo.Sp_InputExceltoSQL ‘E:\shiseidodatabase’,’OrderDetail’