Bulk import of Access data into SQL SERVER

Code:

if object_id (' Sp_inputaccesstosql ')  is not null    drop proc  sp_inputaccesstosqlgocreate proc sp_inputaccesstosql@dir nvarchar,--The path to the Access file: such as D : \files@tabname nvarchar ( --) defines the name of the table that is imported into the database and does not need to create asset nocount ondeclare  if it exists @cmd  nvarchar (create table ) #t ([Filename] nvarchar (+)) If right (@dir, 1) <> ' \ '      set @[email protected]+ ' \ ' set  @cmd  = n ' dir  '  +  @dir  +  ' *.mdb '  /b ' insert  #t  exec master. xp_cmdshell  @cmdDELETE   #t  where [filename] is null--Creating tables in 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 ',  ' [email protected]+[filename]+ '; ' Admin ";" ', ' [email protected]+ ') '     FROM  #t        exec (@S) end--start importing files under directory set @s =  ' select @s = @s +  ' INSERT  ' [email protected]+ '  select * from openrowset (     ' microsoft.ace.oledb.12.0 ',  ' [email protected]+[filename]+ '; Admin ";" ", ' [email protected]+ ') '   FROM  #t       exec (@S) set nocount offgo--call:exec sp_inputaccesstosql  ' F:\test ', ' table_name '

Note: Depending on the version of Access installed by the system,microsoft.ace.oledb.12.0 may be replaced with this microsoft.jet.oledb.4.0 .

FAQ:

Q1:sql Server blocked access to component ' xp_cmdshell ' process ' Sys.xp_cmdshell ' ...

A1: Execute the following SQL statement:

sp_configure ' show advanced options ', 1reconfiguregosp_configure ' xp_cmdshell ', 1reconfigurego

Q2:sql Server blocked access to component ' Ad Hoc distributed Queries ' ...

A2: Execute the following SQL statement:

EXEC sp_configure ' Ad Hoc distributed Queries ', 1; RECONFIGURE; GO

Reference: One, two or three

Walker * * *

This article is from "Walker's Journal" blog, please be sure to keep this source http://walkerqt.blog.51cto.com/1310630/1430990