Inserting rows from existing row

April 21, 2010 cruepprich

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.

[sourcecode language=”sql”]
SELECT empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
FROM emp
WHERE ename = ‘SMITH'[/sourcecode]

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	800		20

Now i want to add three more rows of SMITH but i want to increase his salary by 100 and his department by 10 for each added row. I can do this by using the CONNECTY BY clause and the LEVEL pseudo column.

[sourcecode language=”sql”]
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
[/sourcecode]

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	900	30
7369	SMITH	CLERK	7902	17-DEC-80	1000	40
7369	SMITH	CLERK	7902	17-DEC-80	1100	50