将指定文件夹下结构相同的ACCESS文件内指定的表批量导入到SQL数据库中指定的表

问题贴：http://topic.csdn.net/u/20100511/18/85e96b68-9c5b-4d08-8f5f-d4ae685580a3.html?seed=868821069&r=65351262#r_65351262

--------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-05-11 18:29:45

-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Subject:将指定文件夹下结构相同的ACCESS文件内指定的表批量导入到SQL数据库中指定的表

--------------------------------------------------------------------------

IF OBJECT_ID('Sp_InputAccesstoSQL') IS NOT NULL

DROP PROC Sp_InputAccesstoSQL

GO

CREATE PROC Sp_InputAccesstoSQL

@dir NVARCHAR(100),--ACCESS文件存放路径：如D:\Files

@tabname NVARCHAR(50) --ACCESS要导入的表名

AS

SET NOCOUNT ON

DECLARE @cmd NVARCHAR(1000)

CREATE TABLE #t([filename] NVARCHAR(1000))

IF RIGHT(@dir,1)<>'\'

SET @dir=@dir+'\'

SET @cmd = N'dir "' + @dir + '*.mdb" /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.JET.OLEDB.4.0'', '''+@DIR+[filename]+''';''Admin'';'''','+@tabname+')'

FROM #t

EXEC(@S)

END

--开始导入目录下的文件

SET @S = ''

SELECT @S = @S + 'INSERT '+@tabname+' SELECT * FROM OPENROWSET(

''MICROSOFT.JET.OLEDB.4.0'', '''+@DIR+[filename]+''';''Admin'';'''','+@tabname+')'

FROM #t

EXEC(@S)

SET NOCOUNT OFF

GO

--调用示例：

EXEC Sp_InputAccesstoSQL 'D:\files','UserInfo'