- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello, is there a proc sql alternative of Oracle Merge Statement?
http://www.oracletutorial.com/oracle-basics/oracle-merge/
I'm trying to update Oracle table using value in SAS dataset within proc sql. There are multiple values to be updated . As of now, the query looks like below.
Update oracle_tbl
set value_1 = (select value_1 from sas_dataset where id = 1) ,
value_2 = (select value_2 from sas_dataset where id = 1)
where exists( ) and other filters.
I was wondering if there is a way to speed up performance. Please suggest if any. Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You'll get the fastest performance, either with:
1) SQL Passthrough since the code is literally just transferred to the server - and would have to be Oracle SQL anyways
2) Can you describe some more? There may be a faster SAS way to update this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A few considerations:
The fastest is to upload your update table to Oracle into a temp space, and run the update query there using pass-through.
If that's not possible, the next fastest is to delete the old rows, and append the new ones.
Updating one row at a time will always be slow.
Look at the options for the Oracle engine. In this case, the bulkload options and the updatebuff option are probably the ones that matter most.