Search code examples
sql-serverdatabasevisual-studiosql-server-data-tools

Ignore errors in SSDT Post-Deployment script

I need to populate codelists after publishing my database using SSDT. So I've added new post-deployment script to the project and from it I call another scripts using SQLCMD :r command, each inserting data to one table. But if table is already filled, there are primary key constraints violated and whole setup is broken.

How can I suppress errors in post-deployment script? SQLCMD command :on error ignore is not supported.

Solution

  • Why don't you modify your script to avoid reinserting existing values? Using a common table expression, you would have something resembling:

    ;with cte as (select *, row_number() over (partition by ... order by ...) as Row from ... )
  insert into ...
    select ...
    from cte where not exists (...) and cte.Row = 1

    Cannot be more explicit without having your table definition ...