Adding columns to snapshot not working

ericmuijsvanoord commented 7 months ago

I get an error when I add new columns to an existing snapshot (destination is available). Error is ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

This is because this TSQL code is triggered:

 SELECT * INTO 
    [dwh].[processed].[dyn_project_15793]

      , CAST(NULL AS varchar(50)) AS 

      , CAST(NULL AS nvarchar) AS 

      , CAST(NULL AS int) AS 

   FROM [dwh_upgrade_dbt2].[processed].[dyn_project]

I added three new columns to the snapshot called new_varchar, new_int and new_nvarchar.

This highlights another issue, that is the creation of a nvarchar without length (was also in the previous adapter version). We basically get nvarchar(1) at this point.

ericmuijsvanoord commented 7 months ago

I found the issue in snapshot.sql

 {% set tempTable %}

        SELECT * INTO {{tempTableName}} {{columns}} FROM [{{relation.database}}].[{{ relation.schema }}].[{{ relation.identifier }}] {{ information_schema_hints() }}
  {% endset %}

Columns should be after the *, not after the into.

For example in fabric:

{% set tempTable %}
      CREATE TABLE {{tempTableName}}
      AS SELECT * {{columns}} FROM [{{relation.database}}].[{{ relation.schema }}].[{{ relation.identifier }}] {{ information_schema_hints() }}
  {% endset %}

ericmuijsvanoord commented 7 months ago

https://github.com/dbt-msft/dbt-sqlserver/pull/477

dbt-msft / dbt-sqlserver

Adding columns to snapshot not working #476