Solved

Duplicate/Replicate data in table

Posted on 2014-02-04
5
545 Views
Last Modified: 2014-02-05
I have a table of 10,000 rows that I need to multiply into 200,000. I basically want to take the data I have in the table duplicate it until there are 200,000 rows, but the empl id needs to be unique so I plan on using a sequence to create it.  Basic table structure below

Case

Emplid    case      case_desc
1             open     this case is open
2             closed   this case is closed
3             open     this case is open

Should I justinsert into case a (select  seq.nxtval, case, case_desc  from case b); until I reach 200K or is this a more efficient way.
0
Comment
Question by:klpayton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39833151
so, simply multiply the data by 20?
Try this...

change 11000  to be any number at least as big as the current max id


INSERT INTO yourtable(emplid,case,case_desc)
    SELECT 11000 + ROWNUM, CASE, case_desc
      FROM yourtable,
           (    SELECT LEVEL
                  FROM DUAL
            CONNECT BY LEVEL <= 20)
where rownum <= 200000
0
 
LVL 32

Expert Comment

by:awking00
ID: 39833170
Is emplid currently a unique key?
0
 

Author Comment

by:klpayton
ID: 39833177
@awking00 Yes, emplid is a unique key
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39833185
the insert above will create 200000 unique new records,  So, if you have 10000 to start with you'll have 210000 when done.

If that's too many, Change the connect by to be   <= 19


or simply delete the extras when you're done
if you don't have 10000 rows to start, then bump the connect by up to 21 or higher as needed.

I would expect the insert above should take a few seconds at most.  Try it, if it doesn't work, simply do a rollback, if it does, then commit and you're done.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39833225
if you'd prefer, the insert can be entirely self-contained by doing a lookup on the max id and counting the current number of rows and adjusting the output to make sure it's exactly 200000 rows when done

This does assume you have between 1 and 199,999 rows to start with though

INSERT INTO yourtable
    WITH x AS (SELECT MAX(emplid) maxid, COUNT(*) cnt FROM yourtable)
    SELECT maxid + ROWNUM, CASE, case_desc
      FROM yourtable,
           x,
           (    SELECT LEVEL
                  FROM DUAL
            CONNECT BY LEVEL <= FLOOR(200000 / (SELECT cnt FROM x)))
     WHERE ROWNUM <= 200000 - cnt;
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question