# getting database error while appending data from one table to another table

**URL:** <https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993>\
**Category:** Help\
**Created:** [July 5, 2023, 5:46am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993 "2023-07-05T05:46:48Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 5:46am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/1 "2023-07-05T05:46:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 5, 2023, 5:58am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/2 "2023-07-05T05:58:55Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1688536727360799?thread_ts=1688536032.899509&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 6:00am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/3 "2023-07-05T06:00:31Z")

</div>

how will it append to destination table will u pls write the query I am new to dbt

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 5, 2023, 6:03am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/4 "2023-07-05T06:03:20Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1688536994042019?thread_ts=1688536032.899509&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 5, 2023, 6:03am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/5 "2023-07-05T06:03:41Z")

</div>

dbt will automatcially create insert command at run time.

Note: `@rajeshravindran05` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1688537013988769?thread_ts=1688536032.899509&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 5, 2023, 6:08am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/6 "2023-07-05T06:08:17Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1688537289864529?thread_ts=1688536032.899509&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 6:12am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/7 "2023-07-05T06:12:09Z")

</div>

I ran the query but the records are not appended  
incremental model is created

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 6:27am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/8 "2023-07-05T06:27:48Z")

</div>

and how will the table to know to whom to append?

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [July 5, 2023, 6:57am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/9 "2023-07-05T06:57:49Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1688540261837999?thread_ts=1688536032.899509&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 7:08am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/10 "2023-07-05T07:08:22Z")

</div>

![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/3/31594d445714caab39bf61436d194b04b02ab4ad.png)  
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

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 7:08am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/11 "2023-07-05T07:08:59Z")

</div>

![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/0/0517e65574e301e91867ab4cdea5c780288c13a9.png)  
this is destination\_details.csv

---

<div class="post-metadata">

**Author:** ![akashsolanke22](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/akashsolanke22/32/2676_2.png) [@akashsolanke22](https://discourse.getdbt.com/u/akashsolanke22)\
**Post date:** [July 5, 2023, 7:46am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/12 "2023-07-05T07:46:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 8:21am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/13 "2023-07-05T08:21:13Z")

</div>

![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/c/c0674979782072ce6a94f650773bdd74996fffea.png)  
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

---

<div class="post-metadata">

**Author:** ![akashsolanke22](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/akashsolanke22/32/2676_2.png) [@akashsolanke22](https://discourse.getdbt.com/u/akashsolanke22)\
**Post date:** [July 5, 2023, 9:09am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/14 "2023-07-05T09:09:12Z")

</div>

Is the source and target has same number of columns as you were using select \* for your source select query.

---

<div class="post-metadata">

**Author:** ![akashsolanke22](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/akashsolanke22/32/2676_2.png) [@akashsolanke22](https://discourse.getdbt.com/u/akashsolanke22)\
**Post date:** [July 5, 2023, 9:16am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/15 "2023-07-05T09:16:06Z")

</div>

```auto
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 :

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 10:01am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/16 "2023-07-05T10:01:32Z")

</div>

yes ,same number of columns in both tables

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 5, 2023, 10:10am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/17 "2023-07-05T10:10:59Z")

</div>

![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/5/507574abcf30c62ef7aa1e4a6c9b91112fc7ab40.png)  
source table  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/3/3461394466a09f52189162397a5a3afe974dc14e.png)  
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

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/5/5b6a817d746b3d7088912063ffd261fcccd6b40b.png)  
this is the error over hear  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/4/4407f1eed3c32651fc8e354cb044e39de37f0ade.png)  
IF i put double quotes no error  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/f/f9654ea7ba6798db4c4360499f7636caac785aa7.png)

---

<div class="post-metadata">

**Author:** ![akashsolanke22](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/akashsolanke22/32/2676_2.png) [@akashsolanke22](https://discourse.getdbt.com/u/akashsolanke22)\
**Post date:** [July 5, 2023, 12:59pm UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/18 "2023-07-05T12:59:56Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/b/bda0752a0a9cb70ff7a39bd00e983ec72d7b76e6.png)

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

```auto
{{
    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

```auto
{{
    config(
        materialized='incremental',
        unique_key='id',
        incremental_strategy='delete+insert',
    )
}}
SELECT 
id, 
Name, 
markts 
FROM src

```

---

<div class="post-metadata">

**Author:** ![unknownfakir1](https://avatars.discourse-cdn.com/v4/letter/u/a4c791/32.png) [@unknownfakir1](https://discourse.getdbt.com/u/unknownfakir1)\
**Post date:** [July 6, 2023, 8:42am UTC](https://discourse.getdbt.com/t/getting-database-error-while-appending-data-from-one-table-to-another-table/8993/19 "2023-07-06T08:42:55Z")

</div>

Thank you it helped me