sqlbulkcopy

[havejeet]

Dear All,

I have developed C# application to import data from CSV to SQL Server
using sqlBulkCopy. However I am facing couple of problems listed below

1. whole transfer fails when one record has column width issue (source column width is 30 and destination is 20) so how could I eliminate this row? or is there any work around?

2. my destination table has insert trigger defined. How could I manage to execute it as sqlbulkcopy dumps the whole bunch of records and not one by one record?

3. One column in destination table is computed column and is not included in the transfer, but the dependent column is included in source file (based on this column, computed column fills it data, while normal insert) but how do I fill values in computed column while dumping whole bunch of records?

4. If CDC(change data capture) is enabled on my target table, how SQLBulkCopy can log the data changes in the same?

regards
Jeet

regards

[sknake]

This may be inside of a c# application but these are all questions related to MSSQL. In the future please post SQL question to the proper forum.

1. No work around as far as I know. Most people value data integrity and if a single operation fails they want the process to stop in order to rememdy the issue. I would advise the same.

2. http://msdn.microsoft.com/en-us/library/ms187640.aspx

3. Isn't this the same question as #2? Does the trigger calculate the computed column so wouldn't enabling the trigger for bulk copy fix this issue?

4. I'm not sure, never used CDC.

[havejeet]

Thanks for the reply, and of course I will take care of the posting place!

1. It is defined at table design level and not in trigger

thanks
Jeet

1. No work around as far as I know. Most people value data integrity and if a single operation fails they want the process to stop in order to rememdy the issue. I would advise the same.

2. http://msdn.microsoft.com/en-us/library/ms187640.aspx

3. Isn't this the same question as #2? Does the trigger calculate the computed column so wouldn't enabling the trigger for bulk copy fix this issue?

4. I'm not sure, never used CDC.

[sknake]

If #3 is handled at the design level then you must be setting the calculated value in the application that posts data to the database? If that is the case then your solution would be to run an update query after the bulk insert completes. You will need to flag these records or look for a calculated value or null or zero.