Importing All Records Via a Reject Table

Perhaps "Reject Table" in this strategy would be more appropriately termed "import table". It is a reject table in the sense that rejected records remain in it.

The advantage of this strategy is that you import all data in one go. You do not need to rely on an external file halfway into the import task. You can also use constraints and status fields to process import records from the reject table in a controlled manner.

One drawback of this strategy is that you require more database space. You are also likely to need more processing time.

To import all records from an external file via a reject table:

1.	Define and create a reject table if necessary.

2.	Define an import task importing all records into the reject table.

3.	Define a SQL task transferring records from the reject table to the application table.

The basic idea is to write these SQL statements:

INSERT INTO <application table>[(columns)]

SELECT <all records>

FROM <reject table>

DELETE <all records>

FROM <reject table>

WHERE EXISTS <condition to check whether records are inserted in application table>

You have a lot of flexibility here. Instead of using a SQL task you could use corrective constraints or status fields. Import data could be written to more than one application table, etc.

4.	Append the SQL task to the import task (following it). For instance, define an overall job that contains the import task and then the SQL task.

5.	Run the overall job. Rejected records will remain in the reject table.