CODEWITHSUNDEEP
sqloracle-databaseplsql
user2171679
user2171679

Reputation: 11

reading from textfile and then inserting record to oracle table

Hi I have a table student which have few null fields in it. Student# is primary key in this table. I want to insert data into a table Officerecord by selecting data from student table, filling the null fields from a excel or doc file and inserting into Officerecord table.

Student
=======
Student #   name     Course    age
1            Jess     ENG       19
2            Jane               20
3            Kevin    MAT       19
4            Rob                21

In above table course data is missing for student 2 and student 4. We have a course file which contains student # and course data. I want to read data from file and inserting this record to "Officerecord" Table which looks like as below:

Officerecord
===========

OFFICE#        STUDENT#           COURSE

While inserting data in this table i want no row should contain null value related to student.

Please help.

Upvotes: 0

Views: 1230

Answers (3)

Hijesh V L
Hijesh V L

Reputation: 269

you can create an external table for office_records file and join this table with students table and make necessary changes to input to final office_records table.

The advantage of external table is you can just replace the file when data changes, you need not go for loading the file manually.

This helps even if the user does not have the access/knowledge of inserting records from file using sql developer or Toad.

Upvotes: 0

Jeff
Jeff

Reputation: 11

SQL Developer can import Excel files (.xls / .xlsx) directly into a table, but you will need to define the table structure ahead of time. It's as simple as right clicking the table in the list, selecting import data, selecting the file, then matching up the excel columns with the table columns.

Ideally you would use an external table for importing, but I don't believe you can easily maneuver spreadsheets using one.

In any event, once you have loaded both sets of data, you can do a simple update on your Student table to fill in the nulls.

UPDATE Student
SET (COURSE) =
( SELECT COURSE
  FROM Officerecord
  WHERE Student.Student# = Officerecord.Student# )
WHERE COURSE IS NULL;

Note that this will fail if you have two entries in Officerecord for a single student, but your example isn't clear with how you'd want to handle such a situation.

Upvotes: 1

Hugh Jones
Hugh Jones

Reputation: 2694

Copy the text from the source datafile and create a script from it which is a series of update statements.

A programmers file editor should help you here - recortd a macro that turns raw data in to statments of the form update student set course = 'myCourse' where StudentNo = 99 and course is null;

Upvotes: 0

Related Questions