Insert or Update if exists from a staging table to another table

SQL Navigator
1

Hello

I have a process that runs daily that pulls down a csv file imports it and does some conversion and puts that converted data into a staging table.

I then want a plsql script I can automate along with the other process to insert or update the data from the Staging Table to the main table.

I will have several hundred thousand records per day and approximately 25% of them will be data that needs to be updated.

Example

Maintable Contains

COOPID,ACCOUNTID,DATETIME,VALUE
HSCE,300001,9/10/2017 5:00:00.0000000 AM,0.48
HSCE,300001,9/10/2017 6:00:00.0000000 AM,5
HSCE,300001,9/10/2017 7:00:00.0000000 AM,1
HSCE,300001,9/10/2017 8:00:00.0000000 AM,2.3
HSCE,300001,9/10/2017 9:00:00.0000000 AM,1.8
HSCE,300001,9/10/2017 10:00:00.0000000 AM,3
HSCE,300001,9/10/2017 11:00:00.0000000 AM,2.3

StagingTable Contains

COOPID,ACCOUNTID,DATETIME,VALUE
HSCE,300001,9/10/2017 5:00:00.0000000 AM,3.8
HSCE,300001,9/10/2017 6:00:00.0000000 AM,2.6
HSCE,300001,9/10/2017 7:00:00.0000000 AM,1.2
HSCE,300001,9/11/2017 8:00:00.0000000 AM,6.2
HSCE,300001,9/11/2017 9:00:00.0000000 AM,3.3
HSCE,300001,9/11/2017 10:00:00.0000000 AM,2.5
HSCE,300001,9/11/2017 11:00:00.0000000 AM,6.6

I want a script that will automatically put in the new 9/11 data into the MainTable and update the MainTable with the new values for 9/10 This is a small size example for 1 account but the file and table both contain lots of accounts for many years worth of data.

I looked at Merge and other postings however all seem to use Dual instead of looking at 2 tables.

Thanks for the help

2

Sorry I forgot to add my Indexs are COOPID,ACCOUNID,DATETIME

3

Hi

A merge could be possible if the index was on COOPID, ACCOUNID, DATETIME, but not with your current index.

Regards

Martin

4

Think I got it

5

I got this to work or it seems like it does.

merge into meterdatarows a

using (SELECT coopid,

meterid,

datetime,

value

FROM meterdatarowsstage2) b

on (a.coopid = b.coopid and

a.meterid = b.meterid and

a.datetime = b.datetime)

when matched then

update

set a.value = b.value

when not matched then

insert

(a.coopid,

a.meterid,

a.datetime,

a.value)

values

(b.coopid,

b.meterid,

b.datetime,

b.value)

6

Hi

This will add and not update your records.

As i understand you’ll updaate the record based on the time. therefore, you need to separate date and time.

Regards

Martin

7

Hi

Not sure but it does appear to be working.

For example

I received this file on the 29th I broke it down to simplify

201709260500,E,0.3,
201709260600,0.178,
201709260700,0.276,
201709260800,0.242,
201709260900,0.218,
201709261000,0.252,
201709261100,0.204,
201709261200,0.28,
201709261300,0.192,
201709261400,0.242,
201709261500,0.262,
201709261600,0.204,
201709261700,0.3,
201709261800,1.11,
201709261900,0.584,
201709262000,0.502,
201709262100,1.862,
201709262200,0.596,
201709262300,0.968,
201709270000,1.17,
201709270100,0.98,
201709270200,0.37,
201709270300,0.206,
201709270400,0.264

and this file on the 30th

201709270500,E,0.252,
201709270600,0.586,
201709270700,0.288,
201709270800,0.23,
201709270900,0.18,
201709271000,0.27,
201709271100,0.236,
201709271200,0.184,
201709271300,0.276,
201709271400,0.244,
201709271500,0.23,
201709271600,0.428,
201709271700,1.518,
201709271800,3.338,
201709271900,3.776,
201709272000,2.442,
201709272100,3.61,
201709272200,3.466,
201709272300,0.914,
201709280000,2.146,
201709280100,2.774,
201709280200,0.408,
201709280300,0.314,
201709280400,0.318

And in the database I had all the values from the file on the 29th then I ran the process on the file from the 3oth and now I have all of the updated values from the file on the 30th