Oracle insert into for a designated number of records.

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;
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

0
brgdotnetcontractorAuthor 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
0
slightwv (䄆 Netminder) 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) 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

0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
slightwv (䄆 Netminder) 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

0
brgdotnetcontractorAuthor 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.
0
brgdotnetcontractorAuthor 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 ;
0
slightwv (䄆 Netminder) 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.
0
brgdotnetcontractorAuthor Commented:
Thanks Gentlemen for the excellent help.
0
Mark GeerlingsDatabase AdministratorCommented:
"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]".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.