![[CX's MEMO]](image/cx_memo.jpg "[CX's MEMO]")
20040915.zip' MatchData.vbs
' Usage : CScript MatchData.vbs
Const strFileName1 = "D:\home\edu\hatena\20040915\db1.xls" ' table1 として利用
Const strFileName2 = "D:\home\edu\hatena\20040915\db2.xls" ' table2 として利用
Const strFileName3 = "D:\home\edu\hatena\20040915\db3.xls" ' table3 として利用
MatchData
Sub MatchData
Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName3 & ";Extended Properties=Excel 8.0"
Dim strSQL
strSQL = "INSERT INTO [Sheet1$]" & _
" SELECT t2.c1, t2.c2, t2.c3, t2.c4, t2.c5" & _
" FROM [Excel 8.0;database=" & strFileName1 & "].[Sheet1$] AS t1" & _
" INNER JOIN [Excel 8.0;database=" & strFileName2 & "].[Sheet1$] AS t2" & _
" ON (t1.c3 = t2.c3) AND (t1.c2 = t2.c2) AND (t1.c1 = t2.c1)"
cn.Execute strSQL
End Sub[table1]
c1 c2 c3 c4 c5
あ い う ※ △
か き く ○ ◆
さ し す × ◇
[table2]
c1 c2 c3 c4 c5
あ い う × ○
か き く を ■
さ し す ヾ 〒
た ち つ ‖ 〆
な に ぬ 仝 ±
[table3]
c1 c2 c3 c4 c5
[結果]
c1 c2 c3 c4 c5
あ い う × ○
か き く を ■
さ し す ヾ 〒
INSERT INTO
table3
SELECT
table2.c1,
table2.c2,
table2.c3,
table2.c4,
table2.c5
FROM
table1
INNER JOIN
table2
ON
(table1.c3 = table2.c3)
AND (table1.c2 = table2.c2)
AND (table1.c1 = table2.c1);
