insert into

insert into
  table_name1
  values (X, [Y, ...]) | select col_name(s) from table_name2

Here, colName specifies the column name in the target table, which can be in one of the following three forms:

• Unquoted column name colName

• Column name enclosed in double quotes "colName"

• Double-quoted column name prefixed with an underscore _"colName"

Insert using VALUE clause

t=table(`XOM`GS`FB as ticker, 100 80 120 as volume);
t;

insert into t values(`GOOG, 200);
t;

insert into t values(`AMZN`NFLX, 300 250);
t;

insert into t values(('AMD','NVDA'), (60 400));
t;

The above code example can also be written in a way that follows the ANSI SQL standard for inserting multiple rows into the table directly. This alternative approach yields the same result.

insert into t values ('AMD', 60), ('NVDA', 400);
t;

To only insert values for a subset of columns:

insert into t(ticker, volume) values(`UBER`LYFT, 0 0);
t;

Starting from version 3.00.5, insert into supports using a SELECT clause to insert query results directly into the target table.

t1 = table(`XOM`GS`FB as ticker, 100 80 120 as volume)
t1

t2 = table(`GOOG`AMZN`NFLX as ticker, 100 80 120 as volume);
t2

insert into t1(ticker,volume) select ticker,volume from t2

Inserting all columns in the table is equivalent to the following two expressions

insert into t1 select * from t2

insert into t1 t2

t = keyedTable(`id, 1 2 3 as id, 10 20 30 as c, 100 200 300 as c2, 2024.01.01T09:30:00.000 2024.01.01T09:31:00.000 2024.01.01T09:32:00.000 as update_time)
dbName = "dfs://test_duplicate"
if (existsDatabase(dbName))
{
    dropDatabase(dbName)
}
db = database(dbName, VALUE, [1], engine=`PKEY)
pt = db.createDimensionTable (t, `pt, primaryKey=`id).append!(t)

insert into pt (id, c) values (1 2 3 4 5, 0 0 0 40 50) on duplicate key update c = c + 1

insert into pt (id, c, update_time) values (1 2 3, 0 0 0, 0 0 0) on duplicate key update c = c + 1, update_time = now()

insert into pt (id, c, c2, update_time) values (1 2 3, 4 5 6, 0 0 0, 0 0 0) on duplicate key update c2 = values(c), update_time = now()

insert into pt (id, c, c2, update_time) values (1 2 3, 4 5 6, 0 0 0, 0 0 0) on duplicate key update c2 = NULL, update_time = now()