sqlserver 批量导入Excel表格数据

2016-12-28 19:25:44来源:CSDN作者:FanShangLuoShi人点击

第七城市

附: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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROC [dbo].[Sp_InputExceltoSQL]@dir NVARCHAR(100),--EXCEL文件存放路径:@tabname NVARCHAR(50) --定义导入到数据库中的表名ASSET NOCOUNT ONCREATE 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 @cmdDELETE #t WHERE [filename] IS NULL--在SQL中创建表:DECLARE @S nvarchar(MAX)IF OBJECT_ID(@tabname) IS NULLBEGIN    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’

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台