将ACCESS資料批量導入SQL SERVER

代碼：

<code>IF OBJECT_ID(</code><code>'Sp_InputAccesstoSQL'</code><code>) </code><code>IS</code> <code>NOT</code> <code>NULL</code>

<code>    </code><code>DROP</code> <code>PROC Sp_InputAccesstoSQL</code>

<code>GO</code>

<code>CREATE</code> <code>PROC Sp_InputAccesstoSQL</code>

<code>@dir NVARCHAR(100),</code><code>--ACCESS檔案存放路徑：如D:\Files</code>

<code>@tabname NVARCHAR(50) </code><code>--定義導入到資料庫中的表名,如果存在就不需要建立</code>

<code>AS</code>

<code>SET</code> <code>NOCOUNT </code><code>ON</code>

<code>DECLARE</code> <code>@cmd NVARCHAR(1000)</code>

<code>CREATE</code> <code>TABLE</code> <code>#t([filename] NVARCHAR(1000))</code>

<code>IF </code><code>RIGHT</code><code>(@dir,1)&lt;&gt;</code><code>'\' </code>

<code>    </code><code>SET @dir=@dir+'</code><code>\</code><code>'</code>

<code>SET @cmd = N'</code><code>dir </code><code>"' + @dir + '*.mdb"</code> <code>/B</code><code>'</code>

<code>INSERT #t EXEC master..xp_cmdshell @cmd</code>

<code>DELETE #t WHERE [filename] IS NULL</code>

<code>--在SQL中建立表:</code>

<code>DECLARE @S nvarchar(MAX)</code>

<code>IF OBJECT_ID(@tabname) IS NULL</code>

<code>BEGIN</code>

<code>    </code><code>SELECT TOP 1 @S='</code><code>SELECT</code> <code>TOP</code> <code>0 * </code><code>INTO</code> <code>'+ @tabname+'</code> <code>FROM</code> <code>OPENROWSET(</code>

<code>            </code><code>''</code><code>Microsoft.ACE.OLEDB.12.0</code><code>''</code><code>, </code><code>''</code><code>'+@DIR+[filename]+'</code><code>''</code><code>;</code><code>''</code><code>Admin</code><code>''</code><code>;</code><code>''</code><code>''</code><code>,</code><code>'+@tabname+'</code><code>)</code><code>'</code>

<code>    </code><code>FROM #t  </code>

<code>    </code><code>EXEC(@S)</code>

<code>END</code>

<code>--開始導入目錄下的檔案</code>

<code>SET @S = '</code><code>'</code>

<code>SELECT @S = @S + '</code><code>INSERT</code> <code>'+@tabname+'</code> <code>SELECT</code> <code>* </code><code>FROM</code> <code>OPENROWSET(</code>

<code>    </code><code>''</code><code>Microsoft.ACE.OLEDB.12.0</code><code>''</code><code>, </code><code>''</code><code>'+@DIR+[filename]+'</code><code>''</code><code>;</code><code>''</code><code>Admin</code><code>''</code><code>;</code><code>''</code><code>''</code><code>,</code><code>'+@tabname+'</code><code>)</code><code>'  </code>

<code>FROM #t      </code>

<code>EXEC(@S)</code>

<code>SET NOCOUNT OFF</code>

<code>--調用：</code>

<code>EXEC Sp_InputAccesstoSQL '</code><code>F:\test</code><code>','</code><code>table_name'</code>

注意：根據系統安裝access版本的不同，Microsoft.ACE.OLEDB.12.0可能替換為這個Microsoft.Jet.OLEDB.4.0 。

FAQ:

Q1: SQL Server 阻止了對元件 'xp_cmdshell' 的 過程 'sys.xp_cmdshell' 的通路...

A1: 執行以下sql語句：

<code>sp_configure </code><code>'show advanced options'</code><code>,1</code>

<code>reconfigure</code>

<code>go</code>

<code>sp_configure </code><code>'xp_cmdshell'</code><code>,1</code>

Q2: SQL Server 阻止了對元件 'Ad Hoc Distributed Queries' 的通路...

A2: 執行以下sql語句：

<code>EXEC</code> <code>sp_configure</code><code>'Ad Hoc Distributed Queries'</code><code>,1;</code>

<code>RECONFIGURE;</code>

PS、此方法對上百兆的access檔案速度特别慢，請慎重考慮。

*** walker ***

本文轉自walker snapshot部落格51CTO部落格，原文連結http://blog.51cto.com/walkerqt/1430990如需轉載請自行聯系原作者

RQSLT