Copying data from one SQL server to another

Six Stars

Copying data from one SQL server to another

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

 

Employee

Re: Copying data from one SQL server to another

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

 

Highlighted
Six Stars

Re: Copying data from one SQL server to another

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

Six Stars

Re: Copying data from one SQL server to another

Hi @nikhilthampi 

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog