Bulk Update from Text file

Question

Post reply

Bulk Update from Text file

Anu-923127

SSC-Addicted

Points: 499
More actions
April 26, 2010 at 1:29 am

#144259

How to update a particular database table column with the values present in a flat file.
How to perform this bulk update operation.
Regards,
Anu.

Viewing 3 posts - 1 through 2 (of 2 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This can be done in 2 ways (as far as i know)

Solution 1:

1. Use OPENROWSET to first read-out all the data from text log file and use it as a Common Table Expression (CTE)

2. "Join" CTE with your orignal table and Update

Cons of this method :

I haven't test this; Just my assumption that it wil work 😀

Pros:

No "solid" table required to perform this operation; Read, Update and voila, we are done

Solution 2:

1. Use some BULK INSERT or BCP opeartions to imbibe the data to a temporary table

2. "Join" with your orignal table and Update

Cons of this method :

"Solid" table required to perform this operation

Pros:

I have tested it and it works very well

Hope this gets you started 🙂

Cheers!!

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Actually, OPENROWSET requires the data to be fairly solid. True that it's more forgiving than BULK INSERT and BCP especially when it comes to how the first row of column headers is formatted compared to the rest of the rows and compared to how certain delimiters work, but the data still needs to be fairly solid/consistent.

That notwithstanding, all of the methods that ColdCoffee mentioned are tried and true methods and all are available in Books Online (the help system that comes with SQL Server). You can also use a "text" based linked server if the data is "fairly solid" so that you can address the files and directory as if they were tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)