How to Update Database Records with records from a .csv file

Question

Post reply

How to Update Database Records with records from a .csv file

TTboy

SSC Veteran

Points: 251
More actions
April 10, 2008 at 4:31 am

#184350

I'm trying to update existing records in my production database with records that would would be imported from a .csv file. For each existing record to be modified, I only want to update one specific column in that record.
Consequently, my .csv file has only 2 fields for every row. The 1st field is a field that I want to use to lookup the existing records in the destination database that will be modified. That is, the field corresponds to the client id that is in the client table of my destination database. The 2nd field in the .csv file is the value that I want to be used to update the column in the client record.
In addition, when that column is updated I also only want it to append any information that currently exists in that column the column is defined as a text column so does not have any boundry preventing it from having multiple updates.
Every time I try to create a data flow process it seems to fail as a result of trying to insert a new row instead of identifying the correct client record to update and then update only that column of data.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You data flow should be pretty simple:

1) Data source to get data from Excel workbook

2) OLEDB Command transformation

In the OLEDB Command transformation, you will have a SQL statement that looks like:

"UPDATE MyTable SET MyField=COALESCE(MyField,'')+? WHERE MyID=?"

You then have to wire up the parameters in the component.

Remember that this will not notify you if the record is not found.

If this is a one-time update, Excel can do all of the work for you. In a new column in the workbook use a formula to build a bunch of update statements:

="UPDATE MyTable SET MyField=COALESCE(MyField,'')+'" & A2 & "' WHERE MyID='" & A1 & "'"

Copy and paste the statement on each row and then copy the data from the rows into Enterprise Manager.

TTboy SSC Veteran Points: 251 More actions · Answer 2

Thank you for the reply.

I'm running into a bit of a problem though.

I get an error saying "operand datatype text is invalid for the add operator."

I'm assuming it's because the column I want to update is a Text column.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

It is probably because of a conversion from your data in your data flow.

Try converting the variable data in the data flow or right in the update:

"UPDATE MyTable SET MyField=COALESCE(MyField,'')+CONVERT(VARCHAR,?) WHERE MyID=?"

TTboy SSC Veteran Points: 251 More actions · Answer 4

I changed the statement as recommended and now the error message states "The data types text and varchar are incompatible in the add operator.".

TTboy SSC Veteran Points: 251 More actions · Answer 5

Actually, it did work.

I just did the same conversion for the other side.

Thank you so much.