Claus on Code

The new database project in SSDT, is truly a great tool. It is not perfect thou, and one of its shortcomings, is that there is no out of the box way, to define some static/initial data in in a project, when you deploy it.

In all the projects I do, I always want to have some static data in the database. That could be meta data about the solution, customer type, product type etc.

Of course there is a workaround to this. One is to define the data in a post deployment script. To do that, right click your project and click “Add script”. Select the post deployment script.

To maintain data in a table, each time we deploy, we use a merge statement. I’ll use my stock table, that I created in a previous post. Then the SQL will look like this:

MERGE INTO [Stock] AS Target
USING (VALUES
('VWS','Vestas Wind System'),
('DANSKE', 'Danske Bank')
)
AS Source (StockSymbol, StockName)
ON Target.StockSymbol= Source.StockSymbol
-- update matched rows
--(If we don't want the application to update the rows)
WHEN MATCHED THEN
UPDATE SET StockName = Source.StockName
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (StockSymbol, StockName)
VALUES (StockSymbol, StockName)
-- delete rows that are in the target but not the source
--(If we don't want the application to insert data in our table)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Now, just right click your project, and click publish. And badabing, now you have data in your table.

A more pretty way, could be to put the merge statements into SP, and the call the SP’s from the post deployment script. Then the application could also reset the data with a call to a SP.

The conclusion: To prepopulate your solution with data, put the data into a merge statement, and put it into the post deployment script. This is a fine solution as long as you don’t have to many or to big datasets. Hopefully MS will come up with a better solution in the next version of SSDT.