I am in the process of transitioning our eCommerce database to the declarative model of SSDT to move the database development process into our CI pipeline. Using idempotent MERGE statements to manage static data that are executed during the post-deployment step. Works well so far. It would be nice if SSDT projects were enhanced to treat static data maintenance as part of the whole declarative process.
This is the part I am not crazy about.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'some_table_name')
The SELECT statement in the EXISTS function uses strings to find the schema and the table objects. If the schema or the table name changes, the project will still compile but the MERGE statement will never execute. Hence the declarative nature of the process is ignored here.
The SSDT tooling could be enhanced to recognize these statements. But having some type of editor or other tool that helps manage static data would be a great addition.