Conditional logic in PostDeployment.sql script using SQLCMD

by

UPDATE

I’ve now discovered that the if/else syntax above doesn’t work for me because some of my linked scripts require a GO statement. Essentially the :r just imports the scripts inline, so this becomes invalid sytax.

If you need a GO statement in the linked scripts (as I do) then there isn’t any easy way around this, I ended up creating several post deployment scripts and then changing my project to overwrite the main post depeployment script at build time depending on the build configuration. This is now doing what I need, but it seems like there should be an easier way!

For anyone needing the same thing – I found this post useful

So in my project I have the following post deployment files:

  • Script.PostDeployment.sql (empty file which will be replaced)
  • Default.Script.PostDeployment.sql (links to scripts needed for standard data config)
  • Configuration1.Script.PostDeployment.sql (links to scripts needed for a specific data config)

I then added the following to the end of the project file (right click to unload and then right click edit):

  <Target Name="BeforeBuild">
      <Message Text="Copy files task running for configuration: $(Configuration)" Importance="high" />
      <Copy Condition=" '$(Configuration)' == 'Release' " SourceFiles="Scripts\Post-Deployment\Default.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Debug' " SourceFiles="Scripts\Post-Deployment\Default.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
      <Copy Condition=" '$(Configuration)' == 'Configuration1' " SourceFiles="Scripts\Post-Deployment\Configuration1.Script.PostDeployment.sql" DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" OverwriteReadOnlyFiles="true" />
  </Target>

Finally, you will need to setup matching build configurations in the solution.

Also, for anyone trying other work arounds, I also tried the following without any luck:

  1. Creating a post build event to copy the files instead of having to hack the project file XML. i couldn’t get this to work because I couldn’t form the correct path to the post deployment script file. This connect issue describes the problem

  2. Using variables for the script path to pass to the :r command. But I came across several errors with this approach.

More Related Contents:

  1. How can I schedule a job to run a SQL query daily?
  2. Reset identity seed after deleting records in SQL Server
  3. Querying data by joining two tables in two database on different servers
  4. Generate script in SQL Server Management Studio
  5. Procedure or function !!! has too many arguments specified
  6. Do clustered indexes have to be unique?
  7. Using a database table as a queue
  8. How do I change db schema to dbo
  9. Single or multiple databases
  10. SQL Server equivalent of WM_CONCAT function [duplicate]
  11. I dont know sql syntax for my table [closed]
  12. How to cast the DateTime to Time
  13. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  14. Database Design for Revisions?
  15. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  16. Common Table Expression, why semicolon?
  17. T-SQL split string based on delimiter
  18. Address standardization within a database
  19. How do I find duplicates across multiple columns?
  20. Correct use of transactions in SQL Server
  21. SQL recursive query on self referencing table (Oracle)
  22. What are projection and selection?
  23. Can parameterized statement stop all SQL injection?
  24. Stored procedure that exports data into csv files only exports to one file
  25. Is it necessary to create tables each time you connect the derby database?
  26. Venn Diagram for Natural Join
  27. SQL Server: should I use information_schema tables over sys tables?
  28. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  29. Find rows that have the same value on a column in MySQL
  30. Why are foreign keys more used in theory than in practice?

Leave a Comment