| Creating new table [message #72027] |
Tue, 25 February 2003 06:58  |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
Hello
I am trying to create a new table from an existing
table that contains 165,000 rows.
This new table will contain 22,000 rows from the
original table based on selected criteria.
when I do the following:
CREATE TABLE newtable AS SELECT * FROM oldtable
WHERE appealcode IN();
I insert 22,000 specific appealcodes I want inserted
into the newtable however SQLPLus only lists 270
codes then stops. I am assuming there is a limit
of rows that can be selected in SQLPLus.
Is there any way I can work around this to get
the 22,000 rows I want into the newtable.
thanks in advance
|
|
|
|
|
|
| Re: Creating new table [message #72035 is a reply to message #72027] |
Tue, 25 February 2003 17:06  |
bechir
Messages: 23
Registered: November 2001
|
Junior Member |
|
|
|
Try to create a view (appealcode_v) that includes all your IN() values. Then, CREATE TABLE newtable AS select * from appealcode_v.
|
|
|
|
| To Bechir .....Re: Creating new table [message #72047 is a reply to message #72027] |
Wed, 26 February 2003 07:24 |
denni50
Messages: 18
Registered: November 2002
|
Junior Member |
|
|
thanks Bechir
I found a workaround, perhaps not the most efficient,
but it did work within reasonable amount of time.
Upon discovering SQLPlus has a maximun number of
expressions that can be listed(1000)...I created
the newtable first(structure only):
CREATE TABLE newtable AS SELECT * FROM oldtable
WHERE 1=0;
Then I did the following:
Insert INTO newtable SELECT * FROM oldtable WHERE
appealcode IN(1...1000);(and so on...)
Since I had 22,000 rows I had to do this 22 times.
However I simply copied and pasted the appealcodes
1000 at a time and had the entire INSERT
completed within 20 minutes..it really went fast.
Maybe this will help someone else that encounters
the same situation when they get the ora-01795
error message trying to INSERT large numbers of literals using the IN operator.
thanks for your suggestion.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
