2

I have a conceptual question.

I have two databases which have the same structure. One database has already contained a lot of data. These data should be transferred to the other database via Select and Insert.

How can I do this data migration with the highest performance?

My first approach was to sort all the tables in a list where the tables which contain foreign keys will be stored behind the referenced tables. But with this solution it will be impossible to start parallel processing.

The second idea was to create a custom type which contains the tablename and the tablenames of the referenced tables and a bool flag which stores whether the data in the table have been copied. This type is stored for each table in a list. Then I start a new thread that checks before copying whether the referenced tables have already been created for each table. If not, I execute Thread.Sleep() after which I will check it again.

Is there a well performing approach to this problem?

Any suggestions will be helpful.

EDIT: The old database is a SQL Base database. The new database is a ms sql server database.

Bambuk
  • 192
  • 1
  • 15
  • Most database systems have a [bulk copy mechanism](https://msdn.microsoft.com/en-us/library/7ek5da1a(v=vs.110).aspx). Use that. – Heinzi Jan 13 '16 at 09:36
  • 1
    SELECT/INSERT would be the worst way to do this. I would strongly recommend to use Database-Tools for this. Maybe "Database Admistrators" would be a better place for this. http://dba.stackexchange.com/ – Ole Albers Jan 13 '16 at 09:43
  • The problem is, that the old database is a sqlbase database and the new one is a ms sql server. So now i am developing one of these "Database-Tools" – Bambuk Jan 13 '16 at 09:46
  • 2
    Is your data in old database contains foreign keys and they are all obeyed? If yes, for you migration you can drop all indexes and foreign keys in the target database. And when you are done - restore the foreign keys and indexes. – trailmax Jan 13 '16 at 11:21

2 Answers2

2

You may use either:

1) SQL Server Replication

Or 2) SQL Server Merge statement

You may use SQL Server Linked Servers to connect different database platforms (e.g. sql server, mysql, db2, ...)

  • SQL Server Replication wont work in my case, but i will check if there will be a solution with linked servers and the sql server merge statement. thanks :) – Bambuk Jan 13 '16 at 11:59
  • Notice that `merge` statement cannot work with a linked server. You have to write your own `insert` or `update` statements –  Jan 13 '16 at 12:02
  • You dont need a Linked Server. SQLBase UNLOAD will write the Insert statements for you. Simply UNLOAD and you will have all the DDL , DML and data you need to ready for SQlServer re-Load. – Steve Leighton Oct 10 '17 at 02:54
0

Best advice : Stick with SQLBase. Second best advice , use the SQLBase UNLOAD command via SQLTalk. This will write all DDL statements required to recreate the database else where - including Triggers , Stored Procs, Indexes etc. plus all the Data to load , if you use the right options, to an external file . This file can optionally then be edited programmatically if need be to be in a sql server format ( not much difference ) . There are many options to the UNLOAD command which can't be written here in detail , but here's a link to the syntax. enter image description here Note that SQLBase v12 has been released in recent months and performance has increased tenfold. With the right tuning and indexes etc . it will outstrip Sql Server in terms of efficiency and performance. On a 100Gb database our response times have gone from 50 seconds to 3 seconds with no additional work .

ρяσѕρєя K
  • 132,198
  • 53
  • 198
  • 213
Steve Leighton
  • 790
  • 5
  • 15