Inserting R code output(dataframe )in multiple columns of sql table at once

Question

For example I made an SQL table with column names "Names", "Class", "age" and I have a data-frame which I made using R code:

data_structure1<- as.data.frame("Name")
data_structure1
data_structure2<-as.data.frame("Class")
data_structure2
data_structure3<-as.data.frame("age")
data_structure3
final_df<- cbind(data_structure1,data_structure2,data_structure3)
final_df

#dataframe "Name" contains multiple entries as different names of students. #dataframe "Class" contains multiple entries as classes in which student are studying like 1,2,3,4 etc. #dataframe "age" contains multiple entries as ages of students. I want to insert final_df in my SQL table containing column names as "Names", "Class", "age". Below is the command which I am using for inserting only one column in one column of SQL table and this is working fine for me.

title<- sqlQuery(conn,paste0("INSERT INTO AMPs(Names) VALUES('",Names, "')")) 
title

But this time I want to insert a dataframe of multiple columns in multiple columns of SQL table. Please help me with this. Thanks in advance.

When inserting and updating data via RODBC have you tried sqlSave? — AlwaysLearning
– AlwaysLearning, Commented May 12, 2022 at 8:33

amitr · Accepted Answer · 2022-05-12 10:34:46Z

0

Using the DBI package and a package for your database (e.g., RPostgres) you can do something like this, assuming the table already exists:

AMPs <- data.frame(name = c("Foo", "Bar"),  Class = "Student", age = c(21L, 22L))


conn <-   DBI::dbConnect(...) # read doc to set parameters
## Create a parameterized INSERT statement 
db_handle <- DBI::dbSendStatement(
  conn,
  "INSERT INTO AMPs (Name, Class, age) VALUES ($1, $2, $3)"
)
## Passing the data.frame
DBI::dbBind(db_handle, params = unname(as.list(AMPs)))

DBI::dbHasCompleted(db_handle)

## Close statement handle and connection
DBI::dbClearResult(db_handle)
DBI::dbDisconnect(conn)

You may also want to look at DBI::dbAppendTable.

Edit: The example above works with PostgreSQL, and I see that the OP tagged the question with 'sql-server'. I don't have means for testing it with SQL Server, but the DBI interface aims at being general so I believe it should work. One important thing to note is the syntax, and, in particular, the syntax for the bind parameters. In PostgreSQL the parameters are defined as $1 for the first parameter, $2 for the second and so on. This might be different for other databases. I think SQL Server is using ?, and that that the binding is done based on position - i.e., the first ? is bind to the first supplied parameter, the second ? to the second and so on.

edited May 12, 2022 at 10:34

answered May 12, 2022 at 9:02

amitr

9201 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hafiza Hira Bashir Over a year ago

Can somebody please suggest me what I'm doing wrong here because I think there is something wrong with syntax that is why I'm getting an error that is saying me "Column count doesn't match value count at row 1. :#title<- sqlQuery(conn,paste0("INSERT INTO a(b,c) VALUES('",b,",",c, "')"))

amitr Over a year ago

I think the syntax of the INSERT statement is incorrect. The right syntax is "INSERT INTO amps VALUES ('Baz', 'Sec', 30), ('Alice', 'Worker', 28);" - I.e., after VALUES you write tuples of values, where each tuple contain the values corresponding to the table columns. I'd suggest you look at the documentation of the INSERT statement, and if you have access to a SQL console (such as psql for Postgres, or sql+ for Oracle), try the commands from a command line to test your syntax.

Collectives™ on Stack Overflow

Inserting R code output(dataframe )in multiple columns of sql table at once

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related