Veeru wrote:
> 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