0

I would like to insert contents of a dataframe into an existing table in an oracle database.

sqlSave(conn, df[1:3,c(which(names(df) == "x"), which(names(df) == "y"), which(names(df) == "z")], tablename = "A_X", append = TRUE)

I get the error Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : missing columns in »data« because the chosen columns and those of the Oracle table are not matching.

The oracle table has more columns than the data frame, so the non matching columns should be filled with NULL. How can I implement this in R? I would like to include the contents of the df in the SQL code at the bottom as follows:

 INSERT INTO A_X
              VALUES (df[1:3,c(which(names(df) == "x"), which(names(df) == "y"), which(names(df) == "z")], AUTO_ID, NULL,  NULL);

In Oracle SQL this would be possible with the following code:

INSERT INTO A_X
              VALUES (300, 'text', 'text', AUTO_ID, NULL,  NULL);

The second problem is to generate the ID AUTO_ID automatically. I have version Oracle DB 11.2.0.3 and it's not possible to update to version 12c currently.

yPennylane
  • 760
  • 1
  • 9
  • 27
  • I think the expectation from R is most likely to have the df schema be same as the table schema. Why don't you get the top 5 or so from DB and then use the column names on the df with fill.na to get around this. – Drj Apr 24 '17 at 20:30
  • Thanks for this idea. I thought about that, but I'd like to solve my problem without modifying the df. If that's not possible, I'll have to modify it though. – yPennylane Apr 24 '17 at 20:47

1 Answers1

0

Here is an alternate approach.

In stead of just using sqlSave, use a combination of sqlSave & sqlQuery from RODBC or dbExecute from DBI.

You can then write the df as either a permanent or temp table and then wrap the sqlQuery with an INSERT or UPDATE statement to operate on your target table and the temp table. This is not very elegant, but should give you a scalable solution even if the schema changes in future.

Drj
  • 1,176
  • 1
  • 11
  • 26
  • sqlSave isn't working because I have fields of type memo. I don't quite understand what you mean with the combination of `sqlSave` and `sqlQuery`. I tried `sqlSave(connection, df, tablename = "oracle_table", append = TRUE) ` and got the error `Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : 'Calloc' could not allocate memory (18446744071562067968 of 1 bytes)` – yPennylane Apr 25 '17 at 13:49
  • despite what the error says, I do not think it is a memory allocation issue. Can you share a sample of your data set? It does not have to be the real data, but make sure the data types and string size etc are reflective of reality. You should get the same error with the sample data too. I do not have oracle DB, but I am going to try this on sql-server. – Drj Apr 25 '17 at 15:20
  • I'll do that tomorrow. I opened a new thread for this issue: http://stackoverflow.com/questions/43613375/r-data-frame-to-oracle-sql-table-could-not-allocate-memory – yPennylane Apr 25 '17 at 15:22
  • I put a sample data set in the new post. Could you try it with sqlServer? – yPennylane Apr 26 '17 at 07:24