How should I loop an INSERT INTO statement for SQL on R?-CodePudding

    query <- paste0("INSERT INTO [MASTER] ([name], [code], [type]) VALUES(",
                   "'",data$name,"', ", "'",data$code,"', ","'",data$type,"')")
query_execute <- DBI:dbGetQuery(conn = con, statement = query)

So I have a dataframe called data, with various values in name, code and type columns.

How should I config my for loops so that it can go through from row 1 to the end and insert the data on each row separately?

Asking as I can't just insert the entire dataframe using this method, I get

 Error in new_result(connection@ptr, statement) : 
  Expecting a single string value: [type=character; extent=27].

Thanks!

CodePudding user response：

You may set up a simple for loop with DBI::dbSendQuery.

for (q in query) {
  DBI::dbSendQuery(conn = con, statement = q)
}

CodePudding user response：

Iterate over the number of rows, then filter the table at each loop:

for (i in nrow(data)){
  query <- paste0("INSERT INTO [MASTER] ([name], [code], [type]) VALUES(",
               "'", data[i, ]$name, "', ", "'", data[i, ]$code, "', ","'",data[i, ]$type,"')")
  query_execute <- DBI:dbGetQuery(conn = con, statement = query)
}

Take note that you need to include the comma "," inside the square brackets after the index so that it would be filtered row-wise and not column-wise.