SSDT Migrating Data Without Losing It

Ed Elliott, 2015-12-30

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads