小弟要把excel中的数据用vba批量update insert到oracle中。但是逐条update insert的时候由于excel与oracle的交互原因非常的慢,2000条数据传到server要30s所以想试试用plsql的forall提高性能。
我现在的vba程序是如下
Do Until ws2.Cells(j, 1) = ""
selSql = "select count(*) from AAAA where TIC='" & ws2.Cells(j, 1) & "' and IND='" & ws2.Cells(j, 2) & "' and DATA_D=to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')"
rs.Open selSql, conn
If rs.Fields(0) <> 0 Then
conn.Execute ("update AAAA set VALUE=" & ws2.Cells(j, 4) & " where TIC='" & ws2.Cells(j, 1) & "' and IND='" & ws2.Cells(j, 2) & "' and DATA_D=to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')")
'MsgBox ("更新成功")
Else
conn.Execute ("insert into AAAA(TIC,IND,DATA_D,VALUE) values('" & ws2.Cells(j, 1) & "','" & ws2.Cells(j, 2) & "',to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')," & ws2.Cells(j, 4) & ")")
'MsgBox ("插入成功")
End If
想用vba的程序把plsql的forall写进去。
但是对plsql不是太熟悉,烦请大牛给个例子吧。
条件就是,excel里面有和oracle相同的主键就update
没有就insert
谢谢大家
我现在的vba程序是如下
Do Until ws2.Cells(j, 1) = ""
selSql = "select count(*) from AAAA where TIC='" & ws2.Cells(j, 1) & "' and IND='" & ws2.Cells(j, 2) & "' and DATA_D=to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')"
rs.Open selSql, conn
If rs.Fields(0) <> 0 Then
conn.Execute ("update AAAA set VALUE=" & ws2.Cells(j, 4) & " where TIC='" & ws2.Cells(j, 1) & "' and IND='" & ws2.Cells(j, 2) & "' and DATA_D=to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')")
'MsgBox ("更新成功")
Else
conn.Execute ("insert into AAAA(TIC,IND,DATA_D,VALUE) values('" & ws2.Cells(j, 1) & "','" & ws2.Cells(j, 2) & "',to_date('" & ws2.Cells(j, 3) & "','yyyy-MM-dd')," & ws2.Cells(j, 4) & ")")
'MsgBox ("插入成功")
End If
想用vba的程序把plsql的forall写进去。
但是对plsql不是太熟悉,烦请大牛给个例子吧。
条件就是,excel里面有和oracle相同的主键就update
没有就insert
谢谢大家
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != 'BOOKS'
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
WHERE p.category = 'DVD'
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != 'BOOKS'谢谢大家,我觉得我发错地儿了。
我觉得我的效率低不是没有merge的问题。因为只有2000条数据,oracle应该是不到一秒就搞定的。
问题出在vba的循环中去insert 和 update的问题。
哎,我应该发到vba板块去
谢谢大家了啊