SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSDT Migrating Data Without Losing It

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

Ed Elliott's Sql Developer Blog

Ed is a Sql developer who has a mixed background in support, as a dba and as a developer working with a number of languages c, c#, vb, go, assembly with a variety of technologies and is currently trying to make the sql developer community a little bit more agile, one build step at a time!

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...