StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Something like this will work. It uses the <code>INSERT ... SELECT</code> syntax (see an example <a href="http://www.techonthenet.com/sql/insert.php" rel="nofollow">here</a>) to insert multiple rows from a query.</p> <p>To loop, cross join the results with this query, which will give you a table of 500 rows with sequential numbers.</p> <pre><code>select LEVEL as group_id from dual connect by LEVEL <= 500 </code></pre> <p>Here's the final answer. It's untested so you may need to fix and tweak, but that's the general approach:</p> <pre><code>insert into map (account_id, group_id) select account_id, group_id from account where company_id in (select company_id from company where company_name = 'string') cross join (select LEVEL as group_id from dual connect by LEVEL <= 500) </code></pre> <p><strong>Addition</strong> - OP asked how a query including groups 1,000 through 1,500 (instead of 1 through 500) would work...</p> <p>The <code>CONNECT BY LEVEL</code> always has to start at 1, so you'll need to <code>CONNECT BY LEVEL <= 1500</code> in an inner query and filter out 1 through 999 with an outer WHERE clause. Here's an example; the differences are all in the cross-joined query:</p> <pre><code>insert into map (account_id, group_id) select account_id, group_id from account where company_id in (select company_id from company where company_name = 'string') cross join ( select group_id from ( select LEVEL as group_id from dual connect by LEVEL <= 1500) where group_id >= 1000) </code></pre> <p>I don't have Oracle within reach at the moment so this is untested like my prior answer.</p> <p>Also note: going up to 1,500 like this is no problem, but at some point you'll notice a slowdown. I don't know if it will be at 10,000 or 100,000 or a million, but sooner or later you could hit a wall.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload