Quite often when creating a database you will need tables of static data. These range from simple lookups to date dimensions in a data warehouse. You can easily create these in an SSDT project.
Presuming you have already designed your tables and they are present in your project;
- Create a folder in your project called StaticData then add a sql script to generate the data you require, one for each table. For instance I have a date dimension table called Dim_Date and have created a script called Populate_Dim_Date.sql.
- Next create a script called ‘Script.PostDeployment.sql’ in the root of your project by right clicking your project and choosing ‘Add’ then ‘Script’
and choosing ‘user scripts’ in the SQL Server section, followed by ‘Post-Deployment Script’, then clicking the Add button.
This will create a script that runs following the full deployment of your project and you can direct it to step through each of your sql creation scripts in the StaticData folder we created earlier. To do this you simply add a SQLCMD read command to the post deployment script for each of the sql files you want to execute. Here I have added a command to read (include) the script I created for the date dimension.
Note that to prevent an infestation of the red squigglies you will need to select the icon that represents placing the editor in SQLCMD Mode, in my image above it is the icon with a blue border.
Now when you publish your project your tables will be populated according to your scripts – easy peasy!