Initial question (solution comes afterwards):

I have the following challenge: I have an Oracle database where a software (Infor Supplier Exchange) once created tables and filled them with data. This db shall be migrated to SQL Server, then an upgrade of the Infor software shall be executed with the migrated data.

A colleague of mine already used a script by Microsoft to migrated the Oracle db to SQL Server which is now available for me. Even though the "Keep Identity" flag was set, no primary key in the new db has its Identiy (autoincrement) set - but that is needed by the Infor software to add data later.

I found a way via SSMS to change the Identity (as well as its seed) for each relevant db table: Right-click on the table, design, change the "Identity Specification" manually. But I have over 300 tables: The effort would cost hours (and sanity).

I also found out that I can use SSMS's "export data" task. You have to know that the Infor software provides a db installer which creates all necessary tables, keys, identity properties, etc. with an EMPTY database. So I can basically export the data from the "Oracle migrated old db" to the "Infor prepared new db" since they (should) have the same table names, keys etc. - except the Identity property and the user data.

In the export data task you can check "Enable identity insert". The problem is that this SSMS feature aborts when it processes a table with foreign keys where the referenced table does not exist, yet. So I could go through the old db again, execute the "copy data" task for all tables without primary keys first, then try the remaining tables until all data is copied to the new db. But this is again much effort since I have to go back on every error or check all contraints beforehand.

Do you have a better approach? Is it possible to copy data from db A (with 300 tables) to db B (with the same table structure), hoping that a tool solves the correct order of tables because of their foreign key constraints?

If you have questions on the issue I can explain in more detail. Thanks in advance.

Solution:

I solved the task by disabling constraints and triggers temporarily. The steps are:

EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"
EXEC sp_MSForEachTable "DELETE FROM ?"

I had to clear the target database's tables since they are filled with some sample data by the Infor installer. The data export task can append rows or can try to remove existing rows (with same primary keys). But this uses TRUNCATE internally which doesn't work with foreign key contraints, even when they are disabled by the above command.

Next: Execute the SSMS database task "Export data". Ignore datatype conversion errors (some types differ from Oracle-Migration to target SQL schema, like varchar to nvarchar which I checked and judged as not critical).

exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"

CodePudding user response：

Using the vendor's SQL Server database schema and loading the data yourself is typically the correct approach for migrating to SQL Server with packaged software. But there may be additional guidance available from the vendor.

Instead of trying to load the tables in an order that is compatible the foreign key constraints, which is not always even possible, disable all the foreign keys before loading the database and re-enable them after. See eg Temporarily disable all foreign key constraints