Custom My-IN SQL Export Script for DataGrip

My-IN.sql.groovy

/*
 * Available context bindings:
 *   COLUMNS     List<DataColumn>
 *   ROWS        Iterable<DataRow>
 *   OUT         { append() }
 *   FORMATTER   { format(row, col); formatValue(Object, col); getTypeName(Object, col); isStringLiteral(Object, col); }
 *   TRANSPOSED  Boolean
 * plus ALL_COLUMNS, TABLE, DIALECT
 *
 * where:
 *   DataRow     { rowNumber(); first(); last(); data(): List<Object>; value(column): Object }
 *   DataColumn  { columnNumber(), name() }
 */

SEP = ", "
NEWLINE = System.getProperty("line.separator")

OUT.append("(").append(NEWLINE)

def rowIndex = 0
def rowList = ROWS.collect { it }

for (row in rowList) {
    def rowValues = COLUMNS.collect { col -> row.value(col) }
    def rowSize = rowValues.size()

    if (rowSize > 1) {
        OUT.append("    (")
    } else {
        OUT.append("    ")
    }

    for (int colIndex = 0; colIndex < rowSize; colIndex++) {
        def value = rowValues[colIndex]
        def column = COLUMNS[colIndex]
        def isStringLiteral = value != null && FORMATTER.isStringLiteral(value, column)

        if (isStringLiteral) {
            OUT.append("'").append(FORMATTER.formatValue(value, column)).append("'")
        } else {
            OUT.append(FORMATTER.formatValue(value, column))
        }

        if (colIndex < rowSize - 1) {
            OUT.append(SEP)
        }
    }

    if (rowSize > 1) {
        OUT.append(")")
    }

    if (rowIndex < rowList.size() - 1) {
        OUT.append(",")
    }
    OUT.append(NEWLINE)
    rowIndex++
}

OUT.append(")")

This script helps you export selected data in a format suitable for SQL IN clauses. Here are some examples illustrating the effects of the script:

1. Suppose you have a database table containing the following data:

id	name
1	Alice
2	Bob
3	Charlie

By exporting the "id" column using this script, you will obtain the following output format:

(
    1,
    2,
    3
)

This way, you can directly insert this output into the IN clause of your SQL query, such as:

SELECT * FROM users WHERE id IN (
    1,
    2,
    3
);

1. Suppose you have a database table containing the following data:

order_id	product_id
'202300000001'	1001
'202300000002'	1002
'202300000003'	1003

By exporting the "order_id" and "product_id" columns using this script, you will obtain the following output format:

(
    ('202300000001', 1001),
    ('202300000002', 1002),
    ('202300000003', 1003)
)

This way, you can directly insert this output into the IN clause of your SQL query, such as:

SELECT * FROM orders WHERE (order_id, product_id) IN (
    ('202300000001', 1001),
    ('202300000002', 1002),
    ('202300000003', 1003)
);

This script can flexibly handle both single-column and multi-column data exports, saving you time in manually formatting the data and improving the efficiency of writing queries.