How to append data from R to Oracle db table with identity column

I created a table in Oracle like

Create table t1 
(id_record  NUMERIC GENERATED AS IDENTITY START WITH 500000 NOT NULL,
col1 numeric(2,0),
col2 varchar(10),
primary key(id_record))

where id_record is identity column the value of which is generated automatically when appending data to table.

I create a data.frame in R with 2 columns (table_in_R <- data.frame(col1, col2)). Let's skip the values of data frame for simplicity reasons.

When I append data from R to Oracle db using the following code

dbWriteTable(con, 't1', table_in_R, 
         append =T, row.names=F, overwrite = F)

where con is a connection object the error ORA-00947 arises and no data appended.

When I slightly modify my code (append = F, overwrite = T).

dbWriteTable(con_dwh, 't1', table_in_R, 
         append =FALSE, row.names=F, overwrite = TRUE)

the data is appended, but the identity column id_record is dropped.

How can I append data to Oracle db without dropping the identity column?

Solution 1:

I'd never (based on this answer) recommend this one step approach where the dbWriteTabledirectly maintains the target table.

Instead I'd recommend a two step approach, where the R part fills a temporary table (with overwrite = T i.e. DROP and CREATE)

df <- data.frame(col1 = c(as.integer(1),as.integer(0)), col2 = c('x',NA))
dbWriteTable(jdbcConnection,"TEMP", df,   rownames=FALSE, overwrite = TRUE, append = FALSE)

In the second step you simple adds the new rows to the target table using

insert into t1(col1,col2) select col1,col2 from temp;

You may call it direct with a database connection or also from R:

res <-   dbSendUpdate(jdbcConnection,"insert into t1(col1,col2) select col1,col2 from temp")

Note there is a workaround anyway:

1. Define the identity column as

   id_record NUMERIC GENERATED BY DEFAULT ON NULL AS IDENTITY

This configuration of the identity column provides the correct sequence value instead of the NULL value - but you will fail on the above linked problem of Inserting NULL in a Number column.

2. So the second trick is to use a character NA in the data.frame

Add the identity column to your data.frame and fill it with all as.character(NA).

df <- data.frame(id_record =c(as.character(NA),as.character(NA) ), col1 = c(as.integer(1),as.integer(0)), col2 = c('x',NA))
dbWriteTable(jdbcConnection,"T1", df,   rownames=FALSE, overwrite = F, append = T) 

Test works fine, but as mentioned I'd recommend the two step approach.