Add Condition to SQL .sqlproj file that prevents Post Deployment script IF database already has DATA

Question

I'm working on a database deployment project in Visual Studio (.sqlproj), and we're seeding user test data after deployment/update of schema.

This works fine for clean database deploys, but we don't want to seed the data to existing test environments or production.

I need a Condition check, if data exists in any existing tables, not to run the Post Deployment script.

Do I put that Condition inside the nested <ItemGroup>? Is best practice to assign a variable based on a Pre-Deployment Script and then check against that variable?

ie. assign a integer-based variable based on a query select count from tbl Users where X

Then in the Post Deployment script have a condition that checks against that variable?

 <ItemGroup>
  <PostDeploy Include="Script.PostDeployment.sql" Condition="Users < 1"/>
 </ItemGroup>

Answer 1

You can add your post deployment scripts and use the following script model:

IF NOT EXISTS(SELECT 1 FROM [table] WHERE Id = '123')
BEGIN
   INSERT INTO [table] ...
END

OR

IF (SELECT COUNT(*) FROM [table]) = 0
BEGIN
   INSERT INTO [table]
END

You can also check if the table have any data at all using COUNT and then run insert scripts only if there is no data.

If you have different environment you could also define a variable and run a whole script file depending on the target environment.

IF ('$(Environment)' = 'DEV')
BEGIN
    :r .\DevData.sql
END

And in DevData.sql you would have all your data for the DEV environment. This approach would be more user friendly and easier to maintain.