15 Apr 2020
SQL-Insert-Statements-Bulk-100per.sql.groovy
SEP = ", "
QUOTE = "\'"
NEWLINE = System.getProperty("line.separator")
KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_INSERT_INTO = KEYWORDS_LOWERCASE ? "insert into " : "INSERT INTO "
KW_VALUES = KEYWORDS_LOWERCASE ? "values" : "VALUES"
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"
OUT.append(KW_INSERT_INTO)
if (TABLE == null) OUT.append("post")
else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
OUT.append(" (")
COLUMNS.eachWithIndex { column, idx ->
OUT.append(column.name()).append(idx != COLUMNS.size() - 1 ? SEP : "")
}
OUT.append(")").append(NEWLINE).append(KW_VALUES)
def record(columns, dataRow, close) {
OUT.append(NEWLINE).append("(")
columns.eachWithIndex { column, idx ->
def value = dataRow.value(column)
def skipQuote = value.toString().isNumber() || value == null
def stringValue = value != null ? FORMATTER.format(dataRow, column) : KW_NULL
if (DIALECT.getDbms().isMysql()) stringValue = stringValue.replace("\\", "\\\\")
OUT.append(skipQuote ? "": QUOTE).append(stringValue.replace(QUOTE, QUOTE + QUOTE))
.append(skipQuote ? "": QUOTE).append(idx != columns.size() - 1 ? SEP : "")
}
deliMeter = dataRow.last() || close ? ";" : ","
OUT.append(")").append(deliMeter)
}
count = 0
def createInsert(columns, dataRow) {
count++
if (count%100 == 0 && !dataRow.last()) {
record(columns, dataRow, true)
OUT.append(NEWLINE).append(KW_INSERT_INTO)
if (TABLE == null) OUT.append("post")
else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
OUT.append(" (")
COLUMNS.eachWithIndex { column, idx ->
OUT.append(column.name()).append(idx != COLUMNS.size() - 1 ? SEP : "")
}
OUT.append(")").append(NEWLINE).append(KW_VALUES)
return
}
record(columns, dataRow, false)
}
ROWS.each { row -> createInsert(COLUMNS, row) }