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

IMPORTANT

Before executing the sample code in this section, you need to firstly establish connection to TDegnine cloud service, please refer to Connect to TDengine Cloud Service.

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', 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)
    """)
print("affected_row", affected_row)  # 4

view source code

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

_, 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

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.

conn.exec("DROP DATABASE IF EXISTS power").await?;
conn.exec("CREATE DATABASE power").await?;
conn.exec("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)").await?;
conn.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)
").await?;

view source code

const { options, connect } = require("@tdengine/rest");

function checkError(result) {
  if (result.getErrCode() !== undefined) {
    console.log(result.getErrCode(), result.getErrStr());
    process.exit(1);
  }
}

async function test() {
  options.url = process.env.TDENGINE_CLOUD_URL;
  options.query = { token: process.env.TDENGINE_CLOUD_TOKEN };
  let conn = connect(options);
  let cursor = conn.cursor();
  try {
    let result = await cursor.query("DROP DATABASE IF EXISTS power");
    checkError(result);
    result = await cursor.query("CREATE DATABASE power");
    checkError(result);
    result = await cursor.query("CREATE STABLE power.meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT)");
    checkError(result);
    result = await cursor.query("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)");
    checkError(result);
    console.log("AffectedRows:", result.getAffectRows())
  } catch (err) {
    console.log(err);
  }
}

test();

view source code

<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.

SQL Examples​

Insert Single Row​

Insert Multiple Rows​

Insert into Multiple Tables​

Client Library Examples​

Support and Feedback

SQL Examples

Insert Single Row

Insert Multiple Rows

Insert into Multiple Tables

Client Library Examples