Compare Data between database project and database

Question

So, I love the schema compare option that comes with SSDT. I'm new to using it, and it's much more efficient than any other way I've used to keep database schemas updated across developer machines.

However, what I really need is to be able to compare DATA. Is there a version of the database project that will let me include table data? That would make setting up new developer machines much easier because we could pre-fill some of the tables with the correct data without having to worry about individual scripts.

Please tell me I missed something and there's a magic button that will let me do this!

You haven't missed a magic button. There's not really an "include data" option for database projects. The closest you have is to use post-deploy scripts for data inserts/updates and those don't fall in the realm of comparisons. However, there are tools that can generate the appropriate insert statements into a temp table and from there you can run a MERGE statement. Once the initial scripts are generated, adding new data should be relatively easy unless you change the data in those tables frequently. — Peter Schott
– Peter Schott, Commented Apr 16, 2018 at 15:37
@PeterSchott Can you do post-deploy scripts outside of Code First Migrations? — Don Sartain
– Don Sartain, Commented Apr 16, 2018 at 15:44
Well, you can always pick up the SQL scripts and run them, but to do just the pre/post-deploy scripts, I think you'd have to look at the publish options and not push anything else. I think that's doable by turning on all of the exclude options, but I wouldn't make that your normal publish option. .. We never did code first migrations - we always pushed the DB first and tried to make sure that the code would work with old or new objects, barring new params for stored procs and such. .. May need more details for what you're trying to do. — Peter Schott
– Peter Schott, Commented Apr 17, 2018 at 16:01
Mainly, there are essentially dictionaries that drive drop down lists and things like that in our database. When we spin up a new developer, we have to have them either fill them in manually or restore from a backup. It would be great if we could just do a data compare after they load the schema. This way if we add new entries they could load them in that way as well. Makes more sense than keeping track of maintenance scripts, but apparently isn't possible. — Don Sartain
– Don Sartain, Commented Apr 17, 2018 at 18:01
Have you looked into post-deploy scripts? I've done something similar with Red-Gate's tools to script out a table's data into a temp table, then using a MERGE statement with that temp table and the permanent table. They're large scripts, but can work. Alternatively, for new databases, consider short-circuiting the insert part and using BCP after the deploy/publish option. It depends on the size of the tables, of course. — Peter Schott
– Peter Schott, Commented Apr 18, 2018 at 20:56

scar80 · Accepted Answer · 2018-04-16 18:13:37Z

2

There is an option to compare data between databases: Tools | Sql Server | New Data Comparison. You can read more here: https://msdn.microsoft.com/en-us/library/dn266029(v=vs.103).aspx

SSDT project is only for database schema, it doesn't handle data. Of course you can include data as pre/post deployment scripts but it is not compiled so you can't compare it.

edited Apr 16, 2018 at 18:13

answered Apr 16, 2018 at 9:30

scar80

1,7022 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Don Sartain Over a year ago

That only lets me compare between two databases. I need between a database and database project.

scar80 Over a year ago

Sorry, I read to fast. I updated the answer. No possible to compare data from project.

Don Sartain · Accepted Answer · 2018-05-03 21:16:03Z

0

I wound up importing the Microsoft.SqlServer.DacFx NuGet package into my C# solution. This essentially let me leverage the tooling behind Visual Studio's Schema Compare to make an in-memory DACPAC and then deploy that to a new database name.

I've posted a VERY rough usage example on GitHub: https://github.com/dsartain18/DACPAC

answered May 3, 2018 at 21:16

Don Sartain

61712 silver badges29 bronze badges

Collectives™ on Stack Overflow

Compare Data between database project and database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related