April 26, 2010 at 1:29 am
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.
April 26, 2010 at 2:15 am
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!!
April 26, 2010 at 7:03 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply