Access 支援 INSERT INTO target SELECT ... FROM source 語法,可一次將符合條件的資料從來源表複製到結構相同的目標表,無需程式迴圈,效能極佳。本文提供完整副程式 AppendData,含成功後清空來源表、錯誤處理與使用者提示。
<%
Sub AppendData()
Call OpenDB()
Dim zSql
' 複製 zdy 表所有欄位到 zdgz 表 (欄位順序、型別需一致)
zSql = "INSERT INTO zdgz (title, s_name, user_from, image, content, send_date, isture) " & _
"SELECT title, s_name, user_from, image, content, send_date, isture FROM zdy"
Conn.Execute zSql
If Err = 0 Then
Response.Write "
資料合併成功
"
' 成功後清空來源表 (視業務需求)
Conn.Execute "DELETE FROM zdy"
Else
Response.Write "
合併失敗:可能重複執行或資料衝突
"
Err.Clear
End If
End Sub
%>
Sub OpenDB()
Set rs = Server.CreateObject("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("datasource/hhinfo.mdb")
conn.Open
End Sub
WHERE NOT EXISTS)。INSERT INTO TargetTable (col1, col2, col3)
SELECT col1, col2, col3 FROM SourceTable
WHERE condition;
語法相同,效能更強,支援 TOP, OUTPUT 子句。