Issue with entering huge records

Toad for Oracle
1

Hello Everyone,

I am new to this forum. I have created one temporary table where I want to insert 20000 records. But the problem with Toad is that it does not support more than 700 records. Is there any way to solve alternatively.

I have also used import procedure to insert records directly into the table. But for 1500 records, it tooks 1 hour time for insertion.

Can anyone help me simplest procedure .

My table

create table ABC (ID varchar2(10));

Insert into ABC values(‘A1234’, ‘B1231’, ‘c213456’, ‘D1200’,--------------------etc…)

Advance thanks for your help

2

with this statement (as you shown) you must get:

ORA-00913: too many values

but you are saying you insert 70 records … and this confuses…

So could you please say exact ORA error?

Brg

žDamir

3

Thanks Damir,

Suppose take one new scenario.

Suppose I have created one table (ABC) with one variable (ID) where I want to insert 7000 records from the existing table (Exist_ABC containing ID as variable) with billion of records.

So one table is created:–create table ABC (ID varchar2(10));

Then I use the below logic:–>

create table ABC as

select Id from Exist_ABC

where id in

(‘1234’, ‘1143’, ‘1142’ …etc)—toad not suppor more than 700 ID

union

select Id from Exist_ABC

where id in

(‘1114’, ‘11143’, ‘11321’ …etc)

My problem is that when I am passing the value of ID into IN operator, TOAD supports only less than 1000 records (not 6000 records at a time). So for this 6000 records, I have to mention Union operator 6 times for every 1000 record insertion.

If I insert 1 lakh records means I have to define 100 times the union operator which is a tough task. I have used the Import wizard but it it taking much more time.

Is there any alternative way where we can get the result quickly.

Thanks

4

Ok I understand.

You are trying to automate data load based on manual entries … In your case looks like there is no way to make some logic so you are implementing manual logic in wrong way.

  1. I would create a table with those ID and then query will be just exists based…

  2. Could you test that sql in pure Oracle sqlplus (one with 6000 records in one line)? Please give me a respond.

Brg

Damir Vadas

P.S:
“UNION ALL” all should be much quicker then “union” in your example.

P.P.S
What is “1 lakh” ???

5

TOAD supports only less than 1000 records

This is an Oracle limitation, not a Toad limitation. IN lists only allow 1000 elements.

What version of Toad are you using? In recent versions (12.x) of Toad, the Import wizard has an option called "Use Array DML" which will make the import process go much faster (hundreds of times faster cases of slower networks)

6

If I’m understanding your problem, you can get around this by using SQL*Loader in Toad instead of running a massive DML script. It’s quite easy and it’s orders of magnitude faster.

What I’d do is:

  1. Create that temp table

  2. Insert a record or two manually.

  3. Query the table and export the dataset as a SQL Loader file. (This creates a sort of CSV file with instructions for loading the data.)

  4. Add the remaining data to the file in the same format.

  5. Save it as a .CTRL file

  6. Load the data using the SQLLoader Wizard (Database --> Import --> SQLLoader Wizard).

Svend

1 Like