|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Failed to load all the records if any one of the records is invaliI 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 Veeru (Ve***@discussions.microsoft.com) writes:
> I am trying to load the 30 records into the database table. I have What sort of error do you get?> 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. You could try the ROWS_PER_BATCH option and set it to 1, but that may not help depending on the issue. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland Sommarskog
Lets assume that Customer_type is of length 4 in the database table. If we try to load the records having the customer_type more than 4 charecters, we get an error, at the same time remaining records in the data file are valid but these are not loading into database. My intensition is to load all the valid records into the table and reject all invalid records. My data file contains combination of valid and invalid records. Can you suggest me any solution. Regards Veeru Show quote "Erland Sommarskog" wrote: > Veeru (Ve***@discussions.microsoft.com) writes: > > 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. > > What sort of error do you get? > > You could try the ROWS_PER_BATCH option and set it to 1, but that may > not help depending on the issue. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Normally, operations such as this are best done by first loading into a
'Staging' table, them checking that data for abnormalities and correcting it if possible, separating out the failures, and then inserting only the 'cleansed' data into the production tables. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Veeru" <Ve***@discussions.microsoft.com> wrote in message news:780BD136-11F9-4F7E-BDE0-CF35DA64CD26@microsoft.com... > Hi Erland Sommarskog > > Lets assume that Customer_type is of length 4 in the database table. > If we try to load the records having the customer_type more than 4 > charecters, we get an error, at the same time remaining records in the > data > file are valid but these are not loading into database. > > My intensition is to load all the valid records into the table and reject > all invalid records. My data file contains combination of valid and > invalid > records. > > Can you suggest me any solution. > > Regards > Veeru > > "Erland Sommarskog" wrote: > >> Veeru (Ve***@discussions.microsoft.com) writes: >> > 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. >> >> What sort of error do you get? >> >> You could try the ROWS_PER_BATCH option and set it to 1, but that may >> not help depending on the issue. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> Veeru (Ve***@discussions.microsoft.com) writes:
> Lets assume that Customer_type is of length 4 in the database table. If Try setting the batch size to 1. The hour is late, so I don't have the> we try to load the records having the customer_type more than 4 > charecters, we get an error, at the same time remaining records in the > data file are valid but these are not loading into database. time to test, but if you are lucky it will continue after the error, and not give up immediately. Then again, since you are using INSERT from BULK, why not handle it in the select statement: substring(CustomerType, 1, 4) or just filter for them in a WHERE clause? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics