OraFAQ Forum: Server Utilities » to insert data based on the count of col's data

Home » RDBMS Server » Server Utilities » to insert data based on the count of col's data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to insert data based on the count of col's data [message #234387]

Tue, 01 May 2007 06:37

pravin9
Messages: 42
Registered: May 2007

Member

Hi,
My problem is that i have data in this format

empno ename sal deptno

now i want to upload data if deptno has more than 5 records for a certain department otherwise it should not upload data for that department

can anyone help me regarding this

Report message to a moderator

Re: to insert data based on the count of col's data [message #234392 is a reply to message #234387]

Tue, 01 May 2007 06:52

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Upload from what? What do you mean with "upload"?
Oracle version?

Btw, we all have these data/table. Smile

Regards
Michel

Report message to a moderator

Re: to insert data based on the count of col's data [message #234444 is a reply to message #234392]

Tue, 01 May 2007 11:15

pravin9
Messages: 42
Registered: May 2007

Member

Hi,

I want to upload data from a flat file to staging table.
using sql loader

my flat file looks like this

Empno Ename sal deptno

Now i want to insert records into staging table if only the specified dept has more than 5 records otherwise ignore the rows of that dept

thanks for ur time in advance

Report message to a moderator

Re: to insert data based on the count of col's data [message #234448 is a reply to message #234444]

Tue, 01 May 2007 11:36

Michel Cadot
Messages: 68663
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use external table (search for this in this site).
Then you can use any SQL statement you want, for instance counting the number of rows in each department.

Regards
Michel

Report message to a moderator

Re: to insert data based on the count of col's data [message #234450 is a reply to message #234448]

Tue, 01 May 2007 11:41

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It would help if you (finally) decide to post your Oracle version. Because, not all versions support external tables, and I'm not sure you'll upgrade just because of this assignment.

So, another approach would be loading all records into one table and write a query against it in order to fetch only required records (those that have more than 5 records for a department) and insert them into the target table.

Report message to a moderator