Inserting rows from existing rowApril 21, 2010 at 3:30 pm | Posted in Oracle Developement | Leave a comment
Often, in a testing environment, you need to generate some table data. Some of this data may be random, other data may need to follow some rule.
I had a situation where I needed to create a number of rows for a particular table on the fly. I wanted to base the new rows to be inserted on an existing row of the table. But a few values I needed to change. Lets take for example the scott.emp table. I want to add some rows based on the data for employee SMITH.
SELECT empno ,ename ,job ,mgr ,hiredate ,sal ,comm ,deptno FROM emp WHERE ename = 'SMITH'
WITH myrow AS (SELECT empno ,ename ,job ,mgr ,hiredate ,sal ,comm ,deptno FROM emp WHERE ename = 'SMITH') SELECT empno ,ename ,job ,mgr ,hiredate ,sal + (LEVEL*100) sal ,comm ,deptno + (LEVEL*10) deptno FROM myrow CONNECT BY LEVEL <= 3