If you write code to SQL Server then you might be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 300 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.
I have made some changes to the way MergeUi works and also moved it into a new repository (https://github.com/GoEddie/SSDT-DevPack).
The way MergeUi used to work was that it enumerated the schemas and tables in a project and let you create a merge statement in the post-deploy script. The problem with that is that you may want to have different versions of the table for different environments or you may want to put the merge in a different script and either reference it using :r imports or not reference it at all.
The new way it works is that instead of enumerating tables it now enumerates script files (pre, post, included, not included etc) and lets you choose which table to add whether or not the table has been added before.
I hope this makes more sense, it certainly does for me. I have also fixed a few annoyances like the connection string to import data had to be entered in each time and also if you want to import multiple tables you needed to import them one at a time.
What is MergeUi for?
The use case is very narrow, it is for helping to write merge statements for deploying static or reference data using SSDT.
What should I be aware of?
A static table should look like this:
create table role(
role_id int not null primary key clustered,
role_description varchar(25) not null
If you notice there is no identity on role_id and the table only contains the id and the description table for looking up values and returning text to display. If you have defaults or identity columns or add in columns that are not related to the static data then you will need to write your merge statements by hand or use some other tool.
Things to be aware of
The add in does not hook into any of the visual studio events because this is a tool that shouldn’t be used very often. You shouldn’t very often have to change reference data, possibly when you create a project you should import the reference data tables and then modify them occasionally. Because it isn’t used very much I didn’t want to make Visual Studio any slower and so you will need to build your project and click the refresh button to pick up any changes.
How do I use it?
If you have the old version installed you will need to manually remove it as this is a complete new project, hopefully this will be the only time you will need to do that. To remove the old version go to Tools–>Extensions in visual studio, find MergeUi and un-install it.
To install this one, download the vsix from “https://github.com/GoEddie/SSDT-DevPack/tree/master/release” install that, open an ssdt project and go to “view->other windows->MergeUi” when it opens, if it isn’t displaying a tree with your solution in click “Refresh”.
When you have opened it, navigate down the tree to the script file you want to add the merge and you can right click the script file and do one of:
- Add Table - adds a table with no data so you can enter it in manually
- Import Table - adds a table and imports the data from a database
- Import Multiple Tables - imports a list of tables in one go
If the script has any merge statements in then these are displayed and you can edit these. There is no way to delete scripts using the ui and I try to be careful about not deleting anything that I shouldn’t so if you edit the script before refreshing in MergeUi and then saving you may find multiple copies of the merge statement so just delete manually and then refresh in MergeUi to pick up the changes.
SSDT Dev Pack?
I have moved over to this repo as I have a few other tools that I wanted to release and instead of having lots of vsix files to install I thought it would be easier to release one, this may have been a mistake but we’ll see :)
If you need any help then feel free to ping me by email etc or I have created a gitter chat room for the project:
SQL 2016 CTP
I have had some issues with other tools on machines with the ctp installed - if you get any issues and you have it installed let me know but the chances are there will be issues!
What does it look like
It still isn’t beautiful but it is functional:
Aren’t MERGE statements to be avoided?
If you look at the example of a static table and you have less a few thousand rows then MERGE statements are ideal for deploying this sort of thing. If you are doing anything else then MERGE statements, as with any other statement should be tested. There are some issues with MERGE statements in other use cases but for this they are perfect.
November 12, 2015 - 22:21
We don’t use a lot of merges, but something like this definitely makes them easier to use. I’ve found them helpful for populating/updating the base tables that make up our lookup values.
November 16, 2015 - 13:21
Thanks, I agree totally it is
Thanks, I agree totally it is the sort of thing when you set up a project and then every now and then, hopefully this makes life simpler!