Publish data with SSDT?

Question

I have a SSDT-project. When publishing a new version I want to publish/initialize some data in the database as well. Can that be done using SSDT?

Turbidimeter

Answer 1

It can be done, but could be tricky. If you set up a variable in the project that can be used for "New" releases, you could put that in your post-deploy script as a section that would run a series of inserts, but only for that "New" type.

As David mentioned, the better way would likely be to use something like Red-Gate's data compare or run the scripts after creating the database. It's possible to do it in post-deploy scripts, but could prove tricky.

Something like this could work:

IF '$(DeployType)' = 'New'
BEGIN --"New" release scripts
PRINT 'Post-Deploy Scripts for release.'
:r .\InsertScript1.sql
:r .\InsertScript2.sql
--etc
END --"New" release scripts

Concerted

It might work to put in some logic into the post-deploy scripts to check for the existence of the records before attempting to run the DML? – Casting

We do that, but this post deploy script just runs the actual scripts. Inside the script, we check for whether or not the data already exists before inserting it. This would just be a way to only run certain scripts if you use a certain value for your project variable. Also, when we pass in the "New" variable, it is set to run with a "New" Publish profile that always re-creates the database so we don't do much checking in these cases. – Concerted

Answer 2

This isn't possible in SSDT. The current guidance is to use a post-deployment script.

Redgate ReadyRoll provides many experiences familiar to SSDT users, but has improved static data management as well as many other improvements.

Casting

It seems like a post-deployment script means it IS possible though, right? – Alasteir

Well, in as much as anything is possible in code! What I meant is that there's no special support for static data so you have to run your own code in the post deeply script as a workaround. – Casting

Answer 3

We include Merge-scripts automatically, when they are placed in a specific subfolder of the Project.

Greet

We use merge scripts as well and the scripts are auto-generated from another script. These are included explicitly in the post deploy script similar to Peter Schott's answer but without the 'new' check. – Rigidify

Answer 4

depending on what you do, table valued custructors might be something to have a look at:

SELECT *
FROM
  (VALUES
    (101, 'Bikes'),
    (102, 'Accessories'),
    (103, 'Clothes')
  ) AS Category(CategoryID, CategoryName);

These are easily transported and compared by SSDT.

For ore information see https://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/

Getupandgo