You sometimes want to do things like split a table into two or move a column into another table and when you use SSDT or the compare / merge type of deployments it can be hard to migrate the data in a single deploy as you can't insert the data into a table that doesn't exist and you can't drop the data before it has bee migrated. To fix this we can use pre/post deploy scripts in SSDT. The overall process is:
- Pre-Deploy Script, check for column to be migrated
- Save data in new table not in SSDT (you could have it in SSDT if you use it for multiple releases etc)
- Let SSDT drop the column and create the new one - you will need to have the option set allow data loss on incremental deployments
- In the post-deploy copy the data to the new table
To give a run through of this we want to get from this ERD:
to this ERD:
Basically we are moving from having a single line item per order number to a set of line items. So in SSDT we create our new table and delete the old column, then we add a pre-deploy script with:
https://github.com/GoEddie/SSDTDataMigrateDemo/blob/master/src/MigrateDe...
if exists(select * from sys.columns where name = 'item_id' and object_id = object_id('orders'))
begin
if object_id('order_details_migration') is null
begin
create table order_details_migration
(
order_id int not null,
item_id int not null
);
end
insert into order_details_migration(order_id, item_id)
select o.order_id, o.item_id from orders o left outer join order_details_migration mig on o.order_id = mig.order_id where mig.order_id is null;
end;
If you want to play along at home, deploy this project:
https://github.com/GoEddie/SSDTDataMigrateDemo/tree/6a8ebf92c372461b93f0...
The version with the updated table and migration scripts is:
https://github.com/GoEddie/SSDTDataMigrateDemo/tree/cfc61fbcd61e0c110629...
The post deploy script would have:
https://github.com/GoEddie/SSDTDataMigrateDemo/blob/master/src/MigrateDe...
if object_id('order_details_migration') is not null
begin
insert into order_details(order_id, item_id, cost, line_item_number)
select mig.order_id, mig.item_id, si.price, 1 from order_details_migration mig
inner join sales_items si on mig.item_id = si.item_id
left outer join order_details od on mig.order_id = od.order_id and mig.item_id = od.item_id
where od.order_id is null;
delete from order_details_migration where order_id in (select order_id from order_details);
end
You can run these scripts lots of times and they will only do anything when you have work to do so when you add them to your project add a jira ticket or whatever to take out the bits you don't need from the pre-post deploy scripts at a later date - the order_details_migration table will be dropped on the next publish.
Anyway I hope this helps,
Enjoy
Ed
