如何高效将R语言中的data.frame转换为SQL INSERT语句？

问题背景 #

现有如下R数据集：

my_table = data.frame(id = c(1,2,3), name = c("sam", "smith", "sean"), height = c(156, 175, 191), address = c("123 first street", "234 second street", "345 third street"))

对应的表格输出：

id  name height           address
1  1   sam    156  123 first street
2  2 smith    175 234 second street
3  3  sean    191  345 third street

需要将其转换为针对new_table的SQL INSERT语句，期望输出如下：

INSERT INTO new_table ( id, name, height, address ) VALUES
( 1, sam, 156, 123 first street), ( 2, smith, 175, 234 second street), ( 3, sean, 191, 345 third street)

当前的实现代码冗长、低效且易出错：

first_part = "INSERT INTO new_table ("
second_part = paste(colnames(my_table), collapse = ", ")

third_part = c(my_table[1,1], my_table[1,2], my_table[1,3], my_table[1,4])
third_part = paste(third_part , collapse = ", ")

fourth_part = c(my_table[2,1], my_table[2,2], my_table[2,3], my_table[2,4])
fourth_part = paste( fourth_part, collapse = ", ")

fifth_part = c(my_table[3,1], my_table[3,2], my_table[3,3], my_table[3,4])
fifth_part  = paste(fifth_part , collapse = ", ")

final = paste0(first_part,  second_part, "),", " VALUES ", "( ", third_part, " ),", " (" ,fourth_part, " ),", "(", fifth_part, ") ")

高效实现方法 #

方法一：基础R向量化处理 #

利用apply批量处理每行数据，无需手动逐行拼接，适配任意行数的数据集：

# 拼接列名字符串
col_str = paste(colnames(my_table), collapse = ", ")
# 生成每行对应的(值1, 值2, ...)格式字符串
row_vals = apply(my_table, 1, function(row) {
  paste("(", paste(row, collapse = ", "), ")", sep = "")
})
# 拼接完整INSERT语句
insert_stmt = paste0("INSERT INTO new_table (", col_str, ") VALUES ", paste(row_vals, collapse = ", "))

# 输出结果
cat(insert_stmt)

方法二：结合dplyr的简洁实现 #

如果使用tidyverse工具链，可通过rowwise和c_across快速处理每行：

library(dplyr)

# 生成每行的SQL值段字符串
row_strings = my_table %>%
  rowwise() %>%
  mutate(row_val = paste("(", paste(c_across(everything()), collapse = ", "), ")", sep = "")) %>%
  pull(row_val)

# 拼接完整语句
insert_stmt = paste0("INSERT INTO new_table (", paste(colnames(my_table), collapse = ", "), ") VALUES ", paste(row_strings, collapse = ", "))

cat(insert_stmt)

关键优化：标准SQL字符串加引号 #

注意：标准SQL中字符串类型字段值需要用单引号包裹，否则会被识别为列名导致报错。以下是适配标准SQL的版本：

# 基础R版本的标准SQL适配
col_str = paste(colnames(my_table), collapse = ", ")
row_vals = apply(my_table, 1, function(row) {
  # 对字符类型值添加单引号
  row_quoted = sapply(row, function(x) {
    if (is.character(x)) paste0("'", x, "'") else as.character(x)
  })
  paste("(", paste(row_quoted, collapse = ", "), ")", sep = "")
})

insert_stmt = paste0("INSERT INTO new_table (", col_str, ") VALUES ", paste(row_vals, collapse = ", "))
cat(insert_stmt)

生成的标准SQL语句示例：

INSERT INTO new_table (id, name, height, address) VALUES (1, 'sam', 156, '123 first street'), (2, 'smith', 175, '234 second street'), (3, 'sean', 191, '345 third street')

内容的提问来源于stack exchange，提问作者stats_noob