How to work with multiple connections and union tables

[Dragan_Blagojevic]

Hi,

Is it possible to use union tables with source tables from different databases?
I tried it in schema modeler, and it let’s me create union table and save schema.
I then loaded schema in relativity server and table is shown in schema, but DA SQL Browser doesn’t see union table. All other tables in schema are visible, and I can query them from DA SQL Browser.

I’m I dong something wrong here or DA SQL Browser doesn’t work with union tables.

Provided that union table from tables in different databases is possible, would it be possible to save new/modified records, where each field would be updated in corresponding source table? (of course each table keys will be part of union table).

We are about to start redesign of large legacy system and database (600+ tables, almost 50 different applications sharing common database), and until everything is replaced with new databases and applications, we need old one to keep working in parallel.

If something like that is not possible, would it be possible to have updates to table from one database to trigger updates in another database - basically, we will break-up old database into several smaller and completely redesign it, but until everything is finished old database will have to be fully operational, so we will need to write all data changes simultaneously into new and old database. Idea here is that new database will have proper structure, and then whatever new record is inserted/updated, it gets into old database with existing structure.

I know that something like that can be achieved with sql server replication, but it will get quite complex to setup and maintain until all projects are migrated, so I would prefer to use DataAbstract server and to have data written simultaneously into both old and new database.

Thanks
Dragan

[antonk]

Hello

@Dragan_Blagojevic

I’m I dong something wrong here or DA SQL Browser doesn’t work with union tables.

Union Tables can be accessed via DA SQL but currently their @SourceTable field will be set to -1.

@Dragan_Blagojevic

Is it possible to use union tables with source tables from different databases?

Yes. But these unions won’t be accessible via DA SQL and DA LINQ working in DA SQL mode. Also asseccing such unions won’t be very effective in terms of performance because DA will have to swithch the used DB connection on the fly to access data.

@Dragan_Blagojevic

If something like that is not possible, would it be possible to have updates to table from one database to trigger updates in another database - basically, we will break-up old database into several smaller and completely redesign it, but until everything is finished old database will have to be fully operational, so we will need to write all data changes simultaneously into new and old database. Idea here is that new database will have proper structure, and then whatever new record is inserted/updated, it gets into old database with existing structure.

This IS technically possible but that won’t be much easier than replication.

F.e. you can add a handler to the BeforeProcessDelta event of the DataService, analyze a Delta, create your own Delta and execute it using LocalDataAdapter. In this case you’ll have to have both old and new tables in the Schema.

Or you can just create a SQL UPDATE/INSERT command and directly execute it.

Perhaps you could consider let the SQL Server do the job for you - just add BEFORE INSERT/UPDATE/DELETE triggers to the data tables and perform the needed transformations and data inserts there.

Regards