Inserting rows from existing row

April 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'
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.
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
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

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: