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.
- Python
- Java
- Go
- Rust
- Node.js
- C#
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
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)");
_, 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
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?;
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();
<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>
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}");
}
}
IMPORTANT
Use statement is not applicable for cloud service since REST API is stateless.
