Problem with sequence keys with DBT core and SQL Server

Help
1

The problem I’m having

I want to update/insert data in a table where the PK key is an int and is autoupdated at the same time handle it with dbt. My initial idea was to use incremental table. I use SQL Server. Is there a solution? I prefer to create my target tables with Databasproject in VS 2022, så I don’t want to use dbt då change my target table. But if that is the only way. I must get it functional. Has anyone done this?

The context of why I’m trying to do this

dbt with SQL Server

What I’ve already tried

{{ config(
    materialized='incremental',
    alias='FidacReportingModule',
    unique_key=['ReportingModuleId','ReportingModuleVersionId'],
    on_schema_change='ignore'
) }}
select * from {{ ref('int_meta__no_overlapping') }}
{% if is_incremental() %}
    where AddedDatetime > coalesce((select max(AddedDatetime) from {{ this }}), '1900-01-01')
{% endif %}

I have a lot of different ways to add my incremental key ID_sk by using next value of my_seq but dbt doesn’t accept it

Some example code or error messages

Put code inside backticks
  to preserve indentation
    which is especially important 
      for Python and YAML!
2

Hi Roger, this article might be useful to you Surrogate keys in dbt: Integers or hashes? | dbt Developer Blog