klpayton
asked on
Duplicate/Replicate data in table
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.
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.
Is emplid currently a unique key?
ASKER
@awking00 Yes, emplid is a unique key
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this...
change 11000 to be any number at least as big as the current max id
INSERT INTO yourtable(emplid,case,case
SELECT 11000 + ROWNUM, CASE, case_desc
FROM yourtable,
( SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 20)
where rownum <= 200000