Copying data from one SQL server to another

[Anonymous]

Hi,

I have searched everywhere for a definitive solution to this, and haven't yet found one.

Here's my scenario.

I have two SQL server instances:  SERVER\SQL2016 and SERVER\SQL2017

On SERVER\SQL2017 there is a database called "Business" with tables called "Customers" and "Orders"

On SERVER\SQL2016 there is also a database called "Business"

I want to be able to copy the "Customers" and "Orders" tables, including the structure and data, from the SERVER\SQL2017 to the SERVER\SQL2016 server.

I've tried linking the servers in SQL Server Management Studio, and then tried using tDBTableList to iterate through the tables in turn, but SELECT * INTO doesn't work for linked servers in MSSQL, and I don't want to have to create the table in the target database first as it defeats the object of using Talend to automate everything.

Is there a way I could achieve this in Talend?  

Thanks

Steve

[Anonymous]

Hi Steve,

      Could you please try the method specified by Richard where he is using Dynamic schema to migrate the data from source to target DB? The blog link is as shown below.

https://www.talend.com/blog/2019/11/11/migrate-data-between-databases-one-job-using-dynamic-schema/

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

Thank you, I will take a look at this and report back if I have any issues

[Anonymous]

Hi @nthampi 

Apologies for the delay, I am in the middle of a house move so not around much at the moment.  I plan to revisit this once my house move is complete in a few weeks time.

Regards

Steve