Insert Data Using SQL

SQL Examples

Here are some brief examples for INSERT statement. You can execute these statements manually by TDengine CLI or TDengine Cloud Explorer or programmatically by TDengine client libraries.

Insert Single Row

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

INSERT INTO test.d101 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 "d101".

INSERT INTO test.d101 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 "d101" and 1 row into table "d102".

INSERT INTO test.d101 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) test.d102 VALUES (1538548696800, 12.3, 221, 0.31);

For more details about INSERT please refer to INSERT.

Client Library Examples

Here's an smart meters example to show how to use connectors in different languages, to create a super table called meters in a power database, with columns for timestamp, current, voltage, phase, and tags for group ID and location.

IMPORTANT

Before executing the sample code in this section, please create a database named power on theTDengine Cloud - Explorer page.

How to establish connection to TDegnine Cloud service, please refer to Connect to TDengine Cloud Service.

Python

In this example, we use execute method to execute SQL and get affected rows. The variable conn is an instance of class taosrest.TaosRestConnection we just created at Connect Tutorial.

# create super table
conn.execute("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
# insert multiple rows into multiple tables at once. subtables will be created automatically.
affected_row = conn.execute("""
    INSERT INTO 
    power.d1001 USING power.meters TAGS('California.SanFrancisco',1) VALUES 
                            ('2018-10-03 14:38:10.000', 10.30000, 219, 0.31000) 
                            ('2018-10-03 14:38:20.000', 12.60000, 218, 0.33000) 
                            ('2018-10-03 14:38:30.800', 12.30000, 221, 0.31000)
    power.d1002 USING power.meters TAGS('California.SanDiego',2) VALUES 
                            ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)
        """)
print("affected_row", affected_row)  # 4

view source code

Java

stmt.execute("DROP DATABASE IF EXISTS power");
stmt.execute("CREATE DATABASE power");
stmt.execute("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
stmt.execute("INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('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)");

view source code

Go

_, err = taos.Exec("DROP DATABASE IF EXISTS power")
if err != nil {
	fmt.Println("failed to drop database, err:", err)
	return
}
_, err = taos.Exec("CREATE DATABASE power")
if err != nil {
	fmt.Println("failed to create database, err:", err)
	return
}
_, err = taos.Exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)")
if err != nil {
	fmt.Println("failed to create stable, err:", err)
	return
}
result, err := taos.Exec("INSERT INTO power.d1001 USING power.meters TAGS(California.SanFrancisco, 2) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000) ('2018-10-03 14:38:15.000', 12.60000, 218, 0.33000) ('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)")
if err != nil {
	fmt.Println("failed to insert, err:", err)
	return
}
rowsAffected, err := result.RowsAffected()
if err != nil {
	fmt.Println("failed to get affected rows, err:", err)
	return
}
fmt.Println("RowsAffected", rowsAffected) // RowsAffected 4

view source code

Rust

In this example, we use exec method to execute SQL. exec is designed for some non-query SQL statements, all returned data would be ignored.

taos.exec("CREATE STABLE IF NOT EXISTS power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
taos.exec("INSERT INTO
    power.d1001 USING power.meters
        TAGS('California.SanFrancisco', 2)
        VALUES (NOW, 12.3, 219, 0.31000) (NOW - 1s, 12.60000, 218, 0.33000) (NOW - 3s, 12.30000, 221, 0.31000)
    power.d1002 USING power.meters
        TAGS('California.SanFrancisco', 3)
        VALUES ('2018-10-03 14:39:16.650', 23.4, 218, 0.25000)
    ").await?;

view source code

Node.js

const taos = require('@tdengine/websocket');

var url = process.env.TDENGINE_CLOUD_URL;
async function insertData() {
  let conn = null;
  try {
    let conf = new taos.WSConfig(url);
    conf.setDb('test');
    conn = await taos.sqlConnect(conf);
    await conn.exec(
      "insert into cloud using meters tags (1, 'new york') values (now, 1.1, 1, 1.1)"
    );
  } catch (err) {
    throw err;
  } finally {
    if (conn) {
      await conn.close();
    }
  }
}

insertData();

view source code

C#

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="TDengine.Connector" Version="3.0.*" GeneratePathProperty="true" />
  </ItemGroup>
  <Target Name="copyDLLDependency" BeforeTargets="BeforeBuild">
    <ItemGroup>
      <DepDLLFiles Include="$(PkgTDengine_Connector)\runtimes\**\*.*" />
    </ItemGroup>
    <Copy SourceFiles="@(DepDLLFiles)" DestinationFolder="$(OutDir)" />
  </Target>

</Project>

view source code

string createTable = "CREATE STABLE if not exists test.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)";
string insertData = "INSERT INTO test.d1001 USING test.meters TAGS('California.SanFrancisco', 1) VALUES ('2018-10-03 14:38:05.000', 10.30000, 219, 0.31000)" +
                    "test.d1002 USING test.meters TAGS('California.SanFrancisco', 2) VALUES ('2018-10-03 14:38:16.650', 10.30000, 218, 0.25000)" +
                    "test.d1003 USING test.meters TAGS('California.LosAngeles', 3) VALUES ('2018-10-03 14:38:05.500', 11.80000, 221, 0.28000)" +
                    "test.d1004 USING test.meters TAGS('California.LosAngeles', 4) VALUES ('2018-10-03 14:38:05.000', 10.80000, 223, 0.29000) ";

// create database under database named 'test'
IntPtr res = LibTaosWS.WSQuery(conn, createTable);
ValidUpdateExecution(res);
// Free the query result every time when used up it.
LibTaosWS.WSFreeResult(res);

// insert data into the table created in previous step.
res = LibTaosWS.WSQuery(conn, insertData);
ValidUpdateExecution(res);
// Free the query result every time when used up it.
LibTaosWS.WSFreeResult(res);
                    
void ValidUpdateExecution(IntPtr res)
{
    int code = LibTaosWS.WSErrorNo(res);
    if (code != 0)
    {
        throw new Exception($"execute SQL failed: reason: {LibTaosWS.WSErrorStr(res)}, code:{code}");
    }
}

view source code

IMPORTANT

Use statement is not applicable for cloud service since REST API is stateless.