I have created an r data frame that currently has 691221 rows of data and I want to continue to add to this without repeating or having to recreate this df every time. So, I just want to append the new data. The original data is in an sql database that I have to access and this is my first time ever using RODBC library.
#this was my initial query to get the first batch of data and create the 691000 df
locs <- sqlQuery(con, 'SELECT * FROM v_AllLocs', rows_at_time = 1)
now tomorrow for example, I want to only append the new data that comes in. Is there some command in the RODBC libaray that can recognize this from an R object and previous command lines? OR I have a date/time stamp as one of the columns and thought I could reference that somehow. I was thinking something like:
lastloc<-max(locs$acq_time_ak)
new<-sqlQuery(con, 'SELECT * FROM v_AllLocs where acq_time_ak'> lastloc , rows_at_time = 1)
locs<-rbind(locs, new)
However, I don't think sqlQuery can recognize the r object in its line? or the str of last loc is a POSIXct and maybe the sqlQuery database can't recognize this? It doesn't work regardless. Also, technically this is really simplistic because in reality, I have subsets of information within this where I have individual X with a time stamp that may have a different time stamp than individual Y. But at the moment maybe just to get started?... how can I get the latest data to add to the r object?
Or regardless of data within the SQL, can I ask for the latest data the SQL db has since XX date. So no matter any attribute within the database, I just know that as of November 16 2021 any new data coming in would be selected. Then subsequent queries id have to change the date or something?