Migrate data from multiple Postgres tables (doing joins) into a single Azure Sql Server table

[aratirahalkar]

Hello,

We are redesigning the database tables and also migrating from PostgreSql to Azure Sql Server. 

Database schema in current PostgreSql is different than the new Azure Sql server database schema.

We have a case where data from 2 tables - lets say: User, Account needs to go now into a single table in the new schema.

So basically, we need to join the two tables User and Account and then the result of that join is the input for the new target table.

Can this be achieved using Qlik Replicate migration tools?

[john_wang]

Hello @aratirahalkar ,

Welcome to Qlik Community forum and thanks for reaching out here!

Yes, there are several options:

1- Utilizes the source_lookup() function to join the source tables; or

2- Synchronizes the individual tables from PostgreSQL to Server Server, then merge them within SQL Server, or create a view to join these tables  within SQL Server database.

Hope this helps.

John.

[SushilKumar]

Hello @aratirahalkar 

Seems it not possible because both tables have Different Object id. And both tables have different metadata attached to it .  there are two ways you can achieve this but its outside of Qlik replicate.

1. Create Staging table which have all the columns of both tables and then try to replicate it .

  2. Replicate table from Source to target endpoint and create a view on both table and create report on the basis of that view. 

Regards,

Sushil Kumar

[aratirahalkar]

Thanks for the answer @john_wang 

We will be doing this as a one-time migration activity. Just waned to clarify that point in case that changes the answer.

What I understand from your and Sushil's response is that best would be to use Qlik Replicate and get all the data from PostgreSql to Azure Sql Server first as is. And then run joins inside of Azure Sql Server and create entries into the new target tables. 

Is that correct understanding @john_wang @SushilKumar 

[john_wang]

Hello @aratirahalkar ,
Thanks for the feedback.

For one-time migration, the best approach is replicate the entire data to target side first, then merge/join them within target side, no network overloaded, less impact to source side DB.

Regards,

John.