Write data with SQL

Tablestore SDK for Go executes an INSERT statement to write one or more rows to a data table that does not have local transactions enabled.

Prerequisites

Install Tablestore SDK for Go and initialize a client. SQL DML requires version 1.9.2 or later. We recommend that you use the latest version.
For the data table that you want to access, create a mapping table, and make sure that local transactions are not enabled.
If you use a RAM user, grant the SQL_DML permission to the RAM user.

Description

Call the SQLQuery method to execute an INSERT statement and write one row. If the specified primary key already exists, the request fails.

func (client *TableStoreClient) SQLQuery(request *SQLQueryRequest) (*SQLQueryResponse, error)

The following example writes one row to example_table.

request := &tablestore.SQLQueryRequest{Query: `
INSERT INTO example_table (tenant_id, pk, name, score)
VALUES (1, 'row1', 'example', 10)`}

response, err := client.SQLQuery(request)
if err != nil {
    log.Fatalf("SQLQuery failed: %v", err)
}
fmt.Println("AffectedRows:", response.AffectedRows)

Parameters

SQLQueryRequest contains the following parameters.

Name	Type	Description
Query (required)	string	The `INSERT` statement to execute. The statement specifies the mapping table, columns, and values.

Response

SQLQueryResponse contains the following fields that are directly related to the operation result.

Field	Type	Description
AffectedRows	int64	The number of affected rows.
StmtType	SQLStatementType	The SQL statement type. This operation returns `SQL_INSERT`.

Examples

Write multiple rows

An INSERT statement can write up to 200 rows. All rows must have the same partition key and are written as an atomic operation: all rows succeed or all rows fail.

request := &tablestore.SQLQueryRequest{Query: `
INSERT INTO example_table (tenant_id, pk, name, score) VALUES
    (1, 'row1', 'example-a', 10),
    (1, 'row2', 'example-b', 20),
    (1, 'row3', 'example-c', 30)`}

response, err := client.SQLQuery(request)
if err != nil {
    log.Fatalf("SQLQuery failed: %v", err)
}
fmt.Println("AffectedRows:", response.AffectedRows)