Inserting rows from existing row

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 COMM 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

Leave a Reply

Your email address will not be published. Required fields are marked *