I am using postgres database and I want insert data from src to dst
{{ config(materialized=‘incremental’, incremental_type=‘append’) }}
INSERT INTO {{ ref(‘dst’) }} (“Id”, “Name”, “marks”)
SELECT “Id”, “Name”, “marks” FROM {{ ref(‘src’) }}
But getting error 05:41:58 Database Error in model dst_table (models\example\dst_table.sql)
05:41:58 syntax error at or near “INSERT”
05:41:58 LINE 17: INSERT INTO “postgres”.“example”.“dst” (“Id”, “Name”, "marks…
05:41:58 ^
05:41:58 compiled Code at target\run\DataAnalytics\models\example\dst_table.sql
05:41:58
05:41:58 Done. PASS=8 WARN=0 ERROR=1 SKIP=0 TOTAL=9
cant we simply have the select statement and let dbt incremental type take rest of the course.
insert statement may not be required.
Note: @rajeshravindran05 originally posted this reply in Slack. It might not have transferred perfectly.
how will it append to destination table will u pls write the query I am new to dbt
your dst.sql will look like this
{{ config (materialized = ‘incremental’, incremental_type =‘append’) }}
select id, name, marks from {{ ref(‘src’) }}
Note: @rajeshravindran05 originally posted this reply in Slack. It might not have transferred perfectly.
dbt will automatcially create insert command at run time.
Note: @rajeshravindran05 originally posted this reply in Slack. It might not have transferred perfectly.
you may also need to explore is_incremental option if there are more conditions to validate post first run for insert
Note: @rajeshravindran05 originally posted this reply in Slack. It might not have transferred perfectly.
I ran the query but the records are not appended
incremental model is created
and how will the table to know to whom to append?
try running for the second time, it should append the values. without specifying the incremental condition you may encounter duplicate values.
Note: @rajeshravindran05 originally posted this reply in Slack. It might not have transferred perfectly.
source_details.csv
I ran dbt seed which created table in target schema
After that I create model of these above two src.sql and dst.sql in models adding further as I told you want to append src to dst and I followed your query still not appending and I ran second time
this is destination_details.csv
Hello @unknownfakir1 ,
Looks like you were not following dbt best practices, prior to execute the sql’s on dbt always prepare the sql and test it on your database or compile it on dbt and check the output will it be executable on your database or not.
In your query here I can see you have mentioned columns as string type inside “” can you please remove those " ", we are taking input from other table.
{{ config(materialized=‘incremental’, incremental_type=‘append’) }}
INSERT INTO {{ ref(‘dst’) }} (Id, Name, marks)
SELECT Id, Name, marks FROM {{ ref(‘src’) }}
try to compile this and then execute this on your database so you will get to know where is the issue.
1 Like
I ran the simple query on postgres target is the schema so there for Id it is throwing error so if I double quote the error is gone
Is the source and target has same number of columns as you were using select * for your source select query.
create table source (id int, name varchar,marks int);
select * from source;
insert into source values (1,'test',40);
select * from source;
create table dest (id int, name varchar,marks int);
insert into dest (id,name,marks)
select * from source;
select * from dest;
Output :
CREATE TABLE
id | name | marks
----+------+-------
(0 rows)
INSERT 0 1
id | name | marks
----+------+-------
1 | test | 40
(1 row)
CREATE TABLE
INSERT 0 1
id | name | marks
----+------+-------
1 | test | 40
(1 row)
yes ,same number of columns in both tables
source table
destination table
PS**:I created this two table from csv files using dbt seed command**
And Now I want to append source table to destination
this is the error over hear
IF i put double quotes no error
great thanks for your replay, I could see you where using insert statement where as dbt will try to create the table as per your dbt file name. Thats why it will throw the error
Instead of that if you use your dbt file name as distination table name and if you remove the insert statement it will work. another thing you can remove the incremental_type=‘append’ and use can use incremental_strategy=‘append’ or ‘delete+insert’.
File Name : dest.sql
{{
config(
materialized='incremental',
unique_key='id',
incremental_strategy='append',
)
}}
SELECT
id,
Name,
markts
FROM src
where id not in (select id from dest )
I am using here where statement to allow only unique records.
File Name: dest.sql
{{
config(
materialized='incremental',
unique_key='id',
incremental_strategy='delete+insert',
)
}}
SELECT
id,
Name,
markts
FROM src
1 Like
