Oracle insert into for a designated number of records.

brgdotnet
brgdotnet used Ask the Experts™
on
For the below insert, how would I modify the query to only insert a specified number of records? For example if the number of records to insert is specified in NumberOfRecordsToInsert, then how would I modify the while statement below?

Declare NumberOfRecordsToInsert INT;
NumberOfRecordsToInsert := 400
INSERT INTO target_table (Idcol, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE IdCol = 380;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Do you want 400 of the exact same row?

If that select returns 1 row:

INSERT INTO target_table (Idcol, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE IdCol = 380 connect by level <= NumberOfRecordsToInsert ; 

Open in new window

brgdotnetcontractor

Author

Commented:
The exact same row will not always be returned. Imagine two tables Table1 and Table2, where Table 2 has no data, and the table structures are identical. Suppose we only wanted to copy the first 3 records of Table 1 into Table 2. How would that be done, if you apply these two tables to my original question?

Also I forgot to mention that IdCol will always have a value of 1.(Don't ask why it is set to 1 please, thats just the way it is )
Table 1
IdCol         Col2     Col3
1                Jim       Famoli
1                Karmi  Seloni
1                Carl      Mc Farlin
1                Drey    Foxton
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try this:
INSERT INTO target_table (Idcol, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE IdCol = 380 and rownum <= NumberOfRecordsToInsert ; 

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Note that using rownum will prevent the ability to sort the rows prior to insert.  If you have that requirement, there are a couple ways around it.

INSERT INTO target_table (Idcol, col2, col3)
select * from (
SELECT col1,
       col2,
       col3
FROM source_table
WHERE IdCol = 380 
order by col1,col2
)
where rownum <= NumberOfRecordsToInsert ; 

Open in new window


Another option using row_number:
INSERT INTO target_table (Idcol, col2, col3)
select col1,col2,col3 from (
SELECT col1,
       col2,
       col3,
    row_number() over(order by col1,col2) rn
FROM source_table
WHERE IdCol = 380
)
where rn <= NumberOfRecordsToInsert ; 

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
As slightwv suggested, Oracle does not offer a way to limit the number of inserts directly.  But, since you are using a sub-query to provide the values to insert, and since Oracle offers a way to restrict the number of rows returned by a query (rownum), you can use that combination.  But, as he indicated, you cannot use "rownum" and "order by" at the same level of an Oracle query.  So, if you need both of these, you need a multi-level query with "order by " at the inner level and "rownum" at the outer level.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I keep forgetting the new (well not so new now) features and Mark's post reminded me:
12c introduced LIMIT.
SELECT col1,
       col2,
       col3
FROM source_table
WHERE IdCol = 380 
order by col1,col2
fetch first NumberOfRecordsToInsert rows only

Open in new window

brgdotnetcontractor

Author

Commented:
Thanks guys. It looks like some great solutions here. I don't need to sort, so where rownum <= NumberOfRecordsToInsert ;  might be the best approach. I will try it out.
brgdotnetcontractor

Author

Commented:
Thanks SlightWV, I think your solution below will work for me because I am not concerned with sorting.
I was wondering though. given your query below, is there a way to target a range of records to be copied?
For example if I want the second and third records to be copied (See below) then how can I do that?

Now I know that all of the value for IdCol are the same, so I can't use idCol for the range of values to be copied.
If you think this warrants a new question, let me know, and I will close out the original question and start a new one. Thanks
for your help.


1                Karmi  Seloni
1                Carl      Mc Farlin


INSERT INTO target_table (Idcol, col2, col3)
SELECT IdCol ,
       col2,
       col3
FROM source_table
WHERE IdCol = 380 and rownum <= NumberOfRecordsToInsert ;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't think you need a new question.

I don't understand what you mean by "the second and third records".

You can "get" whatever rows you want.  You just need to tweak the select.
brgdotnetcontractor

Author

Commented:
Thanks Gentlemen for the excellent help.
Mark GeerlingsDatabase Administrator

Commented:
"if I want the second and third records to be copied..."
That presents a challenge, because you cannot simply use this:
where rownum > 1
because that is never true as Oracle is fetching the rows.  Oracle doesn't increment "rownum" until at least one row has been returned.

You can always use: "rownum < [any number you like]".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial