Skip to main content

Insert Using SQL

Introduction​

Application programs can execute INSERT statement through connectors to insert rows. The TDengine CLI can also be used to manually insert data.

Insert Single Row​

The below SQL statement is used to insert one row into table "d1001".

INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31);

Insert Multiple Rows​

Multiple rows can be inserted in a single SQL statement. The example below inserts 2 rows into table "d1001".

INSERT INTO d1001 VALUES (1538548684000, 10.2, 220, 0.23) (1538548696650, 10.3, 218, 0.25);

Insert into Multiple Tables​

Data can be inserted into multiple tables in the same SQL statement. The example below inserts 2 rows into table "d1001" and 1 row into table "d1002".

INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) d1002 VALUES (1538548696800, 12.3, 221, 0.31);

For more details about INSERT please refer to INSERT.

info
  • Inserting in batches can improve performance. Normally, the higher the batch size, the better the performance. Please note that a single row can't exceed 48K bytes and each SQL statement can't exceed 1MB.
  • Inserting with multiple threads can also improve performance. However, depending on the system resources on the application side and the server side, when the number of inserting threads grows beyond a specific point the performance may drop instead of improving. The proper number of threads needs to be tested in a specific environment to find the best number.
warning
  • If the timestamp for the row to be inserted already exists in the table, the behavior depends on the value of parameter UPDATE. If it's set to 0 (the default value), the row will be discarded. If it's set to 1, the new values will override the old values for the same row.
  • The timestamp to be inserted must be newer than the timestamp of subtracting current time by the parameter KEEP. If KEEP is set to 3650 days, then the data older than 3650 days ago can't be inserted. The timestamp to be inserted can't be newer than the timestamp of current time plus parameter DAYS. If DAYS is set to 2, the data newer than 2 days later can't be inserted.

Examples​

Insert Using SQL​

package com.taos.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;


public class RestInsertExample {
    private static Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS-RS://localhost:6041?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }

    private static List<String> getRawData() {
        return Arrays.asList(
                "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
                "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
                "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
                "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
                "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
                "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2",
                "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
                "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3"
        );
    }


    /**
     * The generated SQL is:
     * INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:05.000',10.30000,219,0.31000)
     * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:15.000',12.60000,218,0.33000)
     * power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES('2018-10-03 14:38:16.800',12.30000,221,0.31000)
     * power.d1002 USING power.meters TAGS(California.SanFrancisco, 3) VALUES('2018-10-03 14:38:16.650',10.30000,218,0.25000)
     * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:05.500',11.80000,221,0.28000)
     * power.d1003 USING power.meters TAGS(California.LosAngeles, 2) VALUES('2018-10-03 14:38:16.600',13.40000,223,0.29000)
     * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:05.000',10.80000,223,0.29000)
     * power.d1004 USING power.meters TAGS(California.LosAngeles, 3) VALUES('2018-10-03 14:38:06.500',11.50000,221,0.35000)
     */
    private static String getSQL() {
        StringBuilder sb = new StringBuilder("INSERT INTO ");
        for (String line : getRawData()) {
            String[] ps = line.split(",");
            sb.append("power." + ps[0]).append(" USING power.meters TAGS(")
                    .append(ps[5]).append(", ") // tag: location
                    .append(ps[6]) // tag: groupId
                    .append(") VALUES(")
                    .append('\'').append(ps[1]).append('\'').append(",") // ts
                    .append(ps[2]).append(",") // current
                    .append(ps[3]).append(",") // voltage
                    .append(ps[4]).append(") "); // phase
        }
        return sb.toString();
    }

    public static void insertData() throws SQLException {
        try (Connection conn = getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE DATABASE power KEEP 3650");
                stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
                        "TAGS (location BINARY(64), groupId INT)");
                String sql = getSQL();
                int rowCount = stmt.executeUpdate(sql);
                System.out.println("rowCount=" + rowCount); // rowCount=8
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        insertData();
    }
}

view source code

note
  1. With either native connection or REST connection, the above samples can work well.
  2. Please note that use db can't be used with a REST connection because REST connections are stateless, so in the samples dbName.tbName is used to specify the table name.

Insert with Parameter Binding​

TDengine also provides API support for parameter binding. Similar to MySQL, only ? can be used in these APIs to represent the parameters to bind. From version 2.1.1.0 and 2.1.2.0, parameter binding support for inserting data has improved significantly to improve the insert performance by avoiding the cost of parsing SQL statements.

Parameter binding is available only with native connection.

package com.taos.example;

import com.taosdata.jdbc.TSDBPreparedStatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class StmtInsertExample {
    private static ArrayList<Long> tsToLongArray(String ts) {
        ArrayList<Long> result = new ArrayList<>();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
        LocalDateTime localDateTime = LocalDateTime.parse(ts, formatter);
        result.add(localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli());
        return result;
    }

    private static <T> ArrayList<T> toArray(T v) {
        ArrayList<T> result = new ArrayList<>();
        result.add(v);
        return result;
    }

    private static List<String> getRawData() {
        return Arrays.asList(
                "d1001,2018-10-03 14:38:05.000,10.30000,219,0.31000,California.SanFrancisco,2",
                "d1001,2018-10-03 14:38:15.000,12.60000,218,0.33000,California.SanFrancisco,2",
                "d1001,2018-10-03 14:38:16.800,12.30000,221,0.31000,California.SanFrancisco,2",
                "d1002,2018-10-03 14:38:16.650,10.30000,218,0.25000,California.SanFrancisco,3",
                "d1003,2018-10-03 14:38:05.500,11.80000,221,0.28000,California.LosAngeles,2",
                "d1003,2018-10-03 14:38:16.600,13.40000,223,0.29000,California.LosAngeles,2",
                "d1004,2018-10-03 14:38:05.000,10.80000,223,0.29000,California.LosAngeles,3",
                "d1004,2018-10-03 14:38:06.500,11.50000,221,0.35000,California.LosAngeles,3"
        );
    }

    private static Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS://localhost:6030?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }

    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE DATABASE power KEEP 3650");
            stmt.executeUpdate("USE power");
            stmt.execute("CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) " +
                    "TAGS (location BINARY(64), groupId INT)");
        }
    }

    private static void insertData() throws SQLException {
        try (Connection conn = getConnection()) {
            createTable(conn);
            String psql = "INSERT INTO ? USING meters TAGS(?, ?) VALUES(?, ?, ?, ?)";
            try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
                for (String line : getRawData()) {
                    String[] ps = line.split(",");
                    // bind table name and tags
                    pst.setTableName(ps[0]);
                    pst.setTagString(0, ps[5]);
                    pst.setTagInt(1, Integer.valueOf(ps[6]));
                    // bind values
                    pst.setTimestamp(0, tsToLongArray(ps[1])); //ps[1] looks like: 2018-10-03 14:38:05.000
                    pst.setFloat(1, toArray(Float.valueOf(ps[2])));
                    pst.setInt(2, toArray(Integer.valueOf(ps[3])));
                    pst.setFloat(3, toArray(Float.valueOf(ps[4])));
                    pst.columnDataAddBatch();
                }
                pst.columnDataExecuteBatch();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        insertData();
    }
}

view source code

TDengine
SOC 2 Certified
ISO 27001 Certified
ISO 27017 Certified

TDengine™ is a time-series database purpose-built for Industry 4.0 and Industrial IoT. It enables real-time ingestion, storage, analysis, and distribution of petabytes of data per day, generated by billions of sensors and data collectors. With TDengine making big data accessible, valuable, and affordable, digital transformation has never been easier.

© 2022–2024 TDengine

Products
TDengine EnterpriseTDengine Cloud
Learn
DocsBlogWebinars
Company
AboutNewsroomSecurityLegalPartnersNewsletterContact Us
Social
中