Copy belove script to SQL-Insert-Statements.sql.groovy or create new one
Last active
October 12, 2021 08:28
-
-
Save pasali/a29874cbc939a53325910005bea0d355 to your computer and use it in GitHub Desktop.
SQL Bulk insert export script for DataGrip
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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("MY_TABLE") | |
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) { | |
OUT.append(NEWLINE).append("(") | |
columns.eachWithIndex { column, idx -> | |
def value = dataRow.value(column) | |
def skipQuote = value.toString().isNumber() || value == null || value.toString() == "true" || value.toString() == "false" | |
def stringValue = value != null ? FORMATTER.format(dataRow, column) : KW_NULL | |
if (DIALECT.getFamilyId().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() ? ";" : "," | |
OUT.append(")").append(deliMeter) | |
} | |
ROWS.each { row -> record(COLUMNS, row) } |
A version of this that supports batch inserts may be found here: https://gist.github.com/ProjectCleverWeb/d2362b082af1d7054ebfd464f202ec1b
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks for doing this. Had issues with big databases being imported very slowly. I hacked it so that this would only do 100 values per insert. Please see gist