sqlserver 批量导入Excel表格数据

附：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’