Parse Syntax Error

Help
1

Parse syntax error for INSERT

i have created a temp table with CTE and then inserting the data to the new table in databricks sql with where condition in the select.

I tried checking configuration and updated to the latest

Some example code or error messages


07:03:14    [PARSE_SYNTAX_ERROR] Syntax error at or near 'INSERT'.(line 25, pos 0)
07:03:14
07:03:14    == SQL ==
07:03:14    /* {"app": "dbt", "dbt_version": "1.6.0", "dbt_databricks_version": "1.6.1", "databricks_sql_connector_version": "2.7.0", "profile_name": "US_Walmart", "target_name": "dev", "node_id": "model.US_Walmart.stg_pos_whip_processed_Walmart_CA_Warehouse_Information_CAZ_incremental"} */
07:03:14
07:03:14
07:03:14
07:03:14            create or replace table `hdc_dev`.`pos_whip_processed`.`stg_pos_whip_processed_Walmart_CA_Warehouse_Information_CAZ_incremental`
07:03:14
07:03:14
07:03:14        using delta
07:03:14
07:03:14
07:03:14
07:03:14
07:03:14
07:03:14
07:03:14          as
07:03:14          -- models/update_dim_warehouse.sql
07:03:14
07:03:14
07:03:14
07:03:14    -- Temporary table to hold new rows to be inserted
07:03:14    WITH new_rows AS (
07:03:14        SELECT * FROM `hdc_dev`.`pos_whip_processed`.`stg_pos_whip_processed_Walmart_CA_Warehouse_Information_CAZ_external`
07:03:14    )
07:03:14
07:03:14    INSERT INTO dim_walmart_ca_warehouse_information (
07:03:14    ^^^
07:03:14        whse_nbr,
07:03:14        whse_name,
07:03:14        whse_street_address,
07:03:14        whse_building_address,
07:03:14        whse_city,
07:03:14        whse_state,
07:03:14        whse_zip_code,
07:03:14        load_datetime
07:03:14    )SELECT
07:03:14        n.whse_nbr,
07:03:14        n.whse_name,
07:03:14        n.whse_street_address,
07:03:14        n.whse_building_address,
07:03:14        n.whse_city,
07:03:14        n.whse_state,
07:03:14        n.whse_zip_code,
07:03:14        n.load_datetime
07:03:14    FROM new_rows n
07:03:14    LEFT JOIN dim_warehouse d ON n.whse_nbr = d.whse_nbr
07:03:14    WHERE d.whse_nbr IS NULL
2

i am working with dbt core and i am facing this error and that code is to create temp and load data from main table and compare and insert new rows into the new table.

3

Hi! dbt models should only contain select statements. It is not possible to add some insert into in them. If you are using temp tables in your current implementation you could:

  • make the temp table a CTE, and instead of using insert into just use select
  • or make the temp table its own model and then ref() it from the downstream model
2 Likes
4

Thank you! It worked. Thanks for the help