Copy table records from one environment to another

• Nanthagopal N
• Published: 20 Nov 2002

There is always a need to sync a DB2 database from one environment with another. Instead of using unload or load...

utilities that may require authorization, the following SQL (SELECT) statement can be executed in the source environment. The result can then be stored in a file/dataset that can be run in the destination environment to replicate the records. This SQL can be used in any DB2 version; I tested it on DB2 v7. Execute it one environment (e.g., Production) and its output can be executed on the other environment (e.g., Test). I think it can be used in any other RDBMS too, except that the single quote might need to be replaced with double quotes depending on the database.

Here is the table structure:

 EMP
 
 EMPNO INTEGER
 EMPNAME CHAR(15)
 MGRNO INTEGER
 SALARY DECIMAL(7,2)
 JDATE DATE

Here are the records:

 EMPNO EMPNAME MGRNO SALARY JDATE
 25    ADAMS   15    70000  2001-02-02
 30    BILL    15    60000  2000-06-01

Here is the SQL:

  
 SELECT 'INSERT INTO EMP VALUES ( '||
       EMPNO         ||','||
 ''''||EMPNAME ||'''' ||','|| 
       MGRNO  ||','||
       SALARY  ||','||
 ''''||JDATE ||'''' ||');'
 FROM EMP

Here is the output:

 
 INSERT INTO EMP VALUES (25,'ADAMS',15,70000,'2001-02-02');
 INSERT INTO EMP VALUES (30,'BILL',15,60000,'2000-06-01');
 

The idea is to write a single quote(') to your output, so you have to use four quotes ('''').

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free DB2 tips and scripts.
• Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
• Best Web Links: DB2 tips, tutorials, and scripts from around the Web.