Loading data of one table into another residing on different databases - Netezza

Question

I have a big file which I have loaded in a table in a netezza database using an ETL tool, lets call this database Staging_DB. Now, post some verifications, the content of this table needs to be inserted into similar structured table residing in another netezza DB, lets call this one PROD_DB. What is the fastest way to transfer data from staging_DB to PROD_DB?

1. Should I be using the ETL tool to load the data into PROD_DB? Or,
2. Should the transfer be done using external tables concept?

Answer 1

If there is no transformation need to be done, then better way to transfer is cross database data transfer. As described in Netezza documentation that Netezza support cross database support where the user has object level permission on both databases.

You can check permission with following command -

dbname.schemaname(loggenin_username)=> \dpu username

Please find below working example -

INSERT INTO Staging_DB..TBL1 SELECT * FROM PROD_DB..TBL1

If you want to do some transformation and than after you need to insert in another database then you can write UDT procedures (also called as resultset procedures).

Hope this will help.