How to skip the bad record and continue the loading in SQL loader?

[Vinarashi]

Hi,

I am trying to load data file into database in sql loader. How to skip the bad record alone and continue with loading?

For eg. I am trying to load data file which has 100 records.The 50th record encounters an error.At present only 49 records gets loaded and it stops from 50th record. I want first 49 records as well as 51st to 100th record to be loaded at the same time and 50th records should be written in the bad file. how to skip the 50th record alone and continue the same loading process from 51st record?

Please guide me.
Reply asap.

Thanks,

Flora
reply report

[ck9663]

Define "bad record"...

What are you using to load your data?

~~ CK

[Flora Vinarashi]

Thanks for the reply CK.

I am using SQL Loader. Loading the data file using control file.
Bad record means it just a data error. that particular column is declared as number in database whereas it character in the data file.

How to skip that particular record and continue loading the remaining data?

[ck9663]

What do you mean by "SQL Loader"? Are you using a third-party app? Are you using the Import feature of SSMS? Or BCP? or BULK INSERT? SSIS perhaps? All those can be called SQL Loader...

The best way to handle your case is to import everything as NVARCHAR. Then you can do whatever you want after. That way, no other conversion will be done. You might also consider using format files for further formatting.

If it's an ETL (Extract, Transform, Load), SSIS can handle much of your requirement. Although am old school and will still go to T-SQL, it takes more work, it's faster and you can have more control on your code and how your data will move...

Good Luck!!!

~~ CK