Insert Data

On this page Carat arrow pointing down
Warning:
CockroachDB v20.1 is no longer supported as of November 12, 2021. For more details, refer to the Release Support Policy.

This page has instructions for getting data into CockroachDB with various programming languages, using the INSERT SQL statement.

Before you begin

Make sure you have already:

Note:

Your application should use a retry loop to handle transaction errors that can occur under contention.

Insert rows

When inserting multiple rows, a single multi-row insert statement is faster than multiple single-row statements.

icon/buttons/copy
CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT);
INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250);

For more information about how to use the built-in SQL client, see the cockroach sql reference docs.

icon/buttons/copy
// 'db' is an open database connection

// Insert two rows into the "accounts" table.
if _, err := db.Exec(
    "INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
    log.Fatal(err)
}

For complete examples, see:

icon/buttons/copy
// ds is an org.postgresql.ds.PGSimpleDataSource

try (Connection connection = ds.getConnection()) {
    connection.setAutoCommit(false);
    PreparedStatement pstmt = connection.prepareStatement("INSERT INTO accounts (id, balance) VALUES (?, ?)");

    pstmt.setInt(1, 1);
    pstmt.setInt(2, 1000);
    pstmt.addBatch();

    pstmt.executeBatch();
    connection.commit();
} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n",
                      e.getSQLState(), e.getCause(), e.getMessage());
}

For complete examples, see:

icon/buttons/copy
# conn is a psycopg2 connection

with conn.cursor() as cur:
    cur.execute('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)')

conn.commit()

For complete examples, see:

Bulk insert

If you need to get a lot of data into a CockroachDB cluster quickly, use the IMPORT statement instead of sending SQL INSERTs from application code. It will be much faster because it bypasses the SQL layer altogether and writes directly to the data store using low-level commands. For instructions, see the Migration Overview.

See also

Reference information related to this task:

Other common tasks:


Yes No
On this page

Yes No