Hi


I am trying to load the 30 records into the database table. I have created
one Format File and one .sql file to load the data. I observed one problem
during loading the data into table, if there is any problem in the first
record of my 30 records, the sqlcmd is rejecting the entire datafile (30
records are in this file) instead of rejecting that record and going to load
the next record.

In my .sql file I am using OPENROWSET, the file looks like

INSERT INTO STAGING_TABLE(
STAGING_ID,
OPERATION,
KEY_1
STRING_1
CODE_1)
select
1,
CASE LTRIM(RTRIM(DATA_FILE.OPERATION)) COLLATE Latin1_General_CS_AS WHEN 'L'
THEN 1 WHEN 'I' THEN 1 ELSE NULL END AS OPERATION,
DATA_FILE.CUSTOMER_ID,
DATA_FILE.CUSTOMER_NAME,
DATA_FILE.CUSTOMER_TYPE
FROM OPENROWSET(BULK "C:\Veeru\Customer.idl" , "C:\Veeru\Customer.xml" as
DATA_FILE



Customer.xml is the XML Format File.

my Customer.idl file contains flat file data with pipe seperation

and I am using sqlcmd to load the data

Can you please help me to load all the valid records into the table even the
invalid records fails.

Regards
Veeru

This is called transactional processing and is a good thing (all or
nothing baby!).

Your staging table needs to be more flexible so it can accept crappy
data like you suggest you need - then you can parse and clean it up -
report / catch issues ect.

Cheers!

Veeru wrote: