This page has instructions for getting data into CockroachDB with various programming languages, using the INSERT SQL statement.
Before you begin
Before reading this page, do the following:
- Install CockroachDB.
- Start a local cluster, or create a CockroachDB Cloud cluster.
- Install a Postgres client.
- Connect to the database.
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.
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.
// '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:
// 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:
# 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:
- Migration Overview
IMPORT- Import performance
INSERTUPSERT- Understanding and Avoiding Transaction Contention
- Multi-row DML best practices
- Insert Multiple Rows
Other common tasks:
