Hi All,

Please let me know what is the best way to implement the below conditions:

i have data in staging table(60lak records & more) and have to insert the data into target table only if all the 1st col records(numeric) in staging table r having +ve values.
else -ve values need to be inserted into error table. Also if any -ve values are present then all records/data must be deleted from target table(or data shouldnt be loaded into target table).
what is the best way to do this so tat the performace is not effected...

If this is a daily process and the staging table is identical in terms of setup as the live table, you could use something like Oracle partitions. By creating the staging table with partitions of positive and negative values we can simply move the staging partition of positive values into the live table.

If there are negative values you can truncate the staging table. This is the most efficient way of removing the records.

I don't know how data gets into the staging table, but if it is loaded from a file system (using SQL*Loader), then perhaps you could also consider NOT loading data into a staging table at all, but using the file as an external table. If that's the case, a simple procedure could do the rest.