asked on

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;

slightwv (䄆 Netminder)

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

brgdotnet

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brgdotnet

ASKER

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.

brgdotnet

ASKER

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 ;

slightwv (䄆 Netminder)

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.

brgdotnet

ASKER

Thanks Gentlemen for the excellent help.

Mark Geerlings

"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]".