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

问题贴：http://topic.youkuaiyun.com/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'