Source Control Data Inserts using SSDT

Ash Sharma

8y756

I have a website called "ReportDownloads" which shows a list of reports that Buisness can download for decision making.

As shown here, the table ReportDownloads look like this:

ReportName	SQLQuery
DailyReport	SELECT * FROM....

When the user click on any of the report, the report execute a SQL Query as written in SQLQuery column to build that report.

The requirement is if we want to edit that report, we need to change the SQLQuery, but lets say with multiple release of the same report, we don't want to insert multiple rows for same ReportName, we always want to have one row with most updated SQLQuery. But we do want to keep track of the changes made in each SQLQuery. This I believe can be done using Post Deployment Scripts with SSDT and we would be able to source control our all SQLScripts.

I am having hard time understanding PostDeployement Script.

Can someone please explain, how it can be achieved in Visual Studio 2015 with SQL Server Version 2008

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Not a problem. Please do.

Just wanted to share some thoughts - Always choose the simplest solution to your requirements. Do not over complicate the process where it doesn't have to be. Staff which comes after you shouldn't think that why is this done 'this' way while it could simply have been done 'that' way, etc. Amen.
Ash Sharma8y ago
Yes, just one main table. Sorry I misunderstood it to have multiple triggers but now I got it. Thanks for you suggestion but I still would like to see any answer coming for SSDT
Nitin Sontakke8y ago
How many tables you are talking about here?

I thought there is just one table involved, isn't it?
Ash Sharma8y ago
@Nitin Sontakke
Yes, I wanted to explore Post Deployment Scripts in SSDT for this purpose, your solution does make sense but I am only responsible for few records (corresponding to the number of reports) in the main table which would lead me to create that many triggers and it's production database.
Nitin Sontakke8y ago
I have no idea what SSDT post deployment scripts are either.

However, one quick question. Are you using all of this (SSDT, post deployment script, etc) or just trying to figure it out just to meet your this specific requirement.

This is important. Because there are other ways to meet your goal. For example an audit table for your main table and a trigger which would copy contents in to audit table.

I am sure you are not expecting hundreds of updates on the table which is having thousands of records. Just a few rows in a table and just a few updates once in a while. So audit table would be suitable path too!