Start Mass updating sql database

Mass updating sql database

We've got lots of great SQL Server experts to answer whatever question you can come up with. The fileds in SQL are - ISBN is isbn and price is price, the table these files are in is books.

The UPDATE portion of the code works in an identical fashion to the Implicit Cursor Loop, so this is not really a separate "UPDATE" method as such.

The interesting thing about this method is that it performs a context-switch between PL/SQL and SQL for every FETCH; this is less efficient.

What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full-scans and index rebuilds.

With this one, I set out to demonstrate the advantages of PARALLEL DML, didn't find what I thought I would, and ended up testing 8 different techniques to find out how they differed. The methods covered include both PL/SQL and SQL approaches.

This is necessary for rollback to occur in the event of an error.

When thousands or millions of rows are updated in one transaction, all the information required for rollback is retained in the transaction log until the transaction commits or rolls back.

Another problem occurs when log growth cannot not keep up with the space needed for the update.

SQL will often error indicating the log is full when in fact there is plenty of disk space. SQL Server must keep all transactions in the transaction log until they are committed.

If the action involves thousands to millions of rows, performance can bog down and the transaction log can grow very large, consuming much available disk space.

In fact, updating millions of rows in one query may not even complete because the transaction log fills all available disk space and can no longer grow.

Now you see my dilemma, How do I get a script to read through the Excel file looking for the ISBN numbers, then reading the price for that number and finally, it needs to find that number in the SQL DB and update the price.