CODEWITHSUNDEEP
c#entity-framework-coresqlbulkcopyefcore.bulkextensions
smith
smith

Reputation: 109

Building a data migration tool to migrate data from MySQL to SQL Server

I need to build a C# console application to migrate data from MySql to SQL Server. This would be run for runtime. I can not use other third-party tools since table schemas are different from MySql and SQL Server.

I have to generate destination entities from the source table which are different by table names and column names.

Note: I have a table called Customer. This table will have a couple of navigation properties. I need to add these navigation properties (save the entities of navigation) when bulk inserts the customer.

To save the data what would be the ideal way as bulk insertion? I tried with EFCore.BulkExtensions seems like it is not ideal since it does not set the Id after insertion as EF Core does.

I won't use SqlBulkCopy since there are plenty of tables to migrate.

Upvotes: 0

Views: 387

Answers (1)

gooopil
gooopil

Reputation: 17

Depending on the need for reusability (is it a one off usage?), the need for scalability (is it a huge database with millions upon millions or rows?), you have several solutions. All solutions will follow the same process though.

  1. Read the data from the source database
  2. Optionally Transform it if the structure is different between source and target
  3. Push the data to the target database.

You could use EF to perform the read / write operations, but it might just be a lot faster to just write the SQL yourself since you won't need as much scaffolding. SQL Server also gives you tools to generate the insert statements to save you some time writing them. See What is the best way to auto-generate INSERT statements for a SQL Server table?

Upvotes: 0

Related Questions