How to fetch the data from one server and insert in to other server in SQL Assistance?

Database
Enthusiast

How to fetch the data from one server and insert in to other server in SQL Assistance?

Hi,
How to fetch the data from one server and insert in to other server in SQL Assistance?

e.g
If my source table and target table is in same server, i can do the following

Insert into retail.test_table_Tgt
Select * from retail.test_table_src

How can i do the above, if my source table and target table are different servers without any ETL job?

Can we do it using stored procedures? Please help me to get the syntax...
6 REPLIES
Enthusiast

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

Won't be possible using stored procedure..
You need to go for scripting which will inturn call fastload or mload.
Enthusiast

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

HI Subhash,

In Oracle we can do that using DBLINK,is there any thing like that in Teradata?
Enthusiast

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

Hi,

As far as I know, there is no dblink in teradata like in oracle. but work arounds are always there.... :)
Fastexport ...ftp....and then mload or tpump..... :). Anyone who knows better please suggest. Hope I can speak
to Teradata soon :)

Thanks and regards,
Raja

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

You can export the results in text file from Teradata SQL assistant from Source server.
SELECT * FROM ABC;

Log-in to the target server and import the results from file while running the command below
INSERT INTO TABLE ABC VALUES('?','?','?'); --Assuming there are 3 character columns.

** Exporting and importing option is available in the File menu of Teradata assistant
Enthusiast

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

Raja is correct - there is no option in Teradata to load data from one server to another.
If data volume is less, then you can use the same process as mentioned by Sachin.
But in this case - you've to do manually everytime - dynamically it's not possible.

Better to export & then import [ here also you may face some problem :-) ]

Similar case I had to do in my current project - I've tried this option - but delimiter is always causes problem.
As we had a option to use ETL tool - we went for that to load - it was easy.

If any solutions found - please post...

Regards,
ANIMESH DUTTA
Enthusiast

Re: How to fetch the data from one server and insert in to other server in SQL Assistance?

three solutions:
1.Do EXPORT/IMPORT using SQL assistant.
2.Do FASTEXPORT/FASTLOAD from source to target system.
3.Do source table backup and then restore it on the target system.