Copy table records from one environment to another
Use this SQL statement to sync databases in one environment with another.
There is always a need to sync a DB2 database from one environment with another. Instead of using unload or load...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
Start the conversation
0 comments