-1

We intend to create DACPAC files using SQL database projects and distribute them automatically to several environments, DEV/QA/PROD, using Azure Pipeline. I can make changes to the schema for a table, view, function, or procedure, but I'm not sure how we can update specific data in a table. I am sure this is very common use case but unfortunately I am having hard time implementing it.

Any idea how can I automate creating/updating/deleting a row for a table?

E.g.: update myTable set myColumn = 5 where someColumn = 'condition'
Alvin
  • 290
  • 5
  • 25
  • I will agree with both of the answers - generally speaking you don't want to code massive CRUD operations as part of SSDT projects. I could make some allowances for some method that populates lookup tables and the like, but remember that script has to be re-runnable with no errors/unexpected issues. Post-Deploy scripts can work for this, but to put "fix" scripts in there requires discipline to remove it as soon as it's been run in the appropriate environment. It's doable, but you want to make sure it only runs when you expect it to run. – Peter Schott Dec 20 '22 at 15:18

2 Answers2

1

In your database project you can add a Post Deployment Script

Wouter
  • 2,881
  • 2
  • 9
  • 22
0

Do not. Seriously. I found DACPAC always to be WAY too limiting for serious operations. Look how the SQL is generated and - realize how little control you have.

The standard approach is to have deployment scripts that you generate and that do the changes in the database, plus a table in the db tracking which have executed (possibly with a checksum so you do not need t change the name to update them).

You can easily generate them partially by schema compare (and then generate the change script), but those also allow you to do things like data scrubbing and multi step transformations that DACPAC by design cannot efficiently and easily do.

There are plenty of frameworks for this around. They generally belong in the category of developer tools.

TomTom
  • 61,059
  • 10
  • 88
  • 148