Microsoft SQL Server forum - How to update 1000s of items in 50 tables

Author

How to update 1000s of items in 50 tables

rdraider

2005-09-20, 3:24 am

Hi all,
I'm looking for a way to re-number inventory items. The items exist in 50+
tables, hundreds of fields and there are several thousand items. Maybe one
table could hold the list of old & new items, another table holds the list
of tables/fields to update? How can this be done without needing a million
individual update statements?

A brute force method may look like:
update table1 set field1 = newitem where field1 = olditem

update table1 set field2 = newitem where field2 = olditem

update table2 set field1 = newitem where field1 = olditem

Thanks for any input.

Jens

2005-09-20, 3:24 am

Its hard to give youan exact answer without having more information, a
good point would be to start at the bottom, the master records, there
you could have two columns holding the old and the new values. If the
key changes going down to deeper granularity you have to do the same
(new / old column).

The tables itself should be at last updates with updating the old
column with tne new column and then dropping (if you don=B4t want to
retain the old information) the old.

Its not quite easy and should be planned carefully (and tested of
course, with only a subset of data). But then you won=B4t need a single
update for every record exisiting.

HTH, Jens Suessmeyer.

Hugo Kornelis

2005-09-20, 8:23 pm

On Tue, 20 Sep 2005 05:46:33 GMT, rdraider wrote:

>Hi all,
>I'm looking for a way to re-number inventory items. The items exist in 50+
>tables, hundreds of fields and there are several thousand items. Maybe one
>table could hold the list of old & new items, another table holds the list
>of tables/fields to update? How can this be done without needing a million
>individual update statements?
>
>A brute force method may look like:
>update table1 set field1 = newitem where field1 = olditem
>
>update table1 set field2 = newitem where field2 = olditem
>
>update table2 set field1 = newitem where field1 = olditem
>
>Thanks for any input.
>
>

Hi rdraider,

If you have all the foreign key constraints in place, and you have
created them with the cascading updates option, it's actually as simple
as

UPDATE MainTable
SET Column = New value
WHERE Column = Old value

The cascading updates will make sure that the change is rolled out to
all other tables.

Since you have many tables, this might take some time. During that time,
all locks takes will make concurrent use of the database nigh on
impossible, so plan to do this during down time.

Also, make sure that your transacion log is big enough to hold all
changes. Grow it manually before starting if needed. Autogrow is quite
slow, since it allocates only a bit of extra disk space at a time.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

rdraider

2005-09-20, 8:23 pm

Hugu,
This database is part of an application. There are no foreign key
constraints and many tables don't even have a primary key. I don't think I
can modify the design of the tables without breaking the app. The
application vendor is of no help.
What if I create update statements to cover all tables and fields (not hard
to do with Excel) then just pass the old and new items as parameters? Is
this possible? If the old and new items were in a separate table, how do I
pass those to the update statements?

Thanks

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:13u0j15kev9puhb
n2p0k6jibbcqsu0cs86@
4ax.com...
> On Tue, 20 Sep 2005 05:46:33 GMT, rdraider wrote:
>
>
> Hi rdraider,
>
> If you have all the foreign key constraints in place, and you have
> created them with the cascading updates option, it's actually as simple
> as
>
> UPDATE MainTable
> SET Column = New value
> WHERE Column = Old value
>
> The cascading updates will make sure that the change is rolled out to
> all other tables.
>
> Since you have many tables, this might take some time. During that time,
> all locks takes will make concurrent use of the database nigh on
> impossible, so plan to do this during down time.
>
> Also, make sure that your transacion log is big enough to hold all
> changes. Grow it manually before starting if needed. Autogrow is quite
> slow, since it allocates only a bit of extra disk space at a time.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis

2005-09-20, 8:23 pm

On Tue, 20 Sep 2005 22:04:30 GMT, rdraider wrote:

>Hugu,
>This database is part of an application. There are no foreign key
>constraints and many tables don't even have a primary key. I don't think I
>can modify the design of the tables without breaking the app. The
>application vendor is of no help.

Hi rdraider,

In that case, you'll have to bite the bullet and use a list of update
statements. One for each table. Do enclose all update statements in a
transaction and include error handling, so that you don't end up with
the new value in half of your tables changed and the old value in the
other half. (Instead of enclosing all in a transaction, you could take a
backup before the conversion starts and restore if something goes
wrong).

>What if I create update statements to cover all tables and fields (not hard
>to do with Excel) then just pass the old and new items as parameters? Is
>this possible?

That's possible, but not very efficient, as the same table has to be
passed over several times (once for each pair of old value/new value).

> If the old and new items were in a separate table, how do I
>pass those to the update statements?

That would be much more efficient. You'd have to set up a table that
holds old value and new value for each possible value of the column (if
a value won;t change, include it with old value equal to new value).
Then the query to change a column in one table would be:

UPDATE table1
SET column1 = (SELECT NewValue
FROM ConversionHelpTable
WHERE OldValue = table1.column1)

If only some of the values need to change and many others remain
unchanged, then it might be more efficient to have only the values that
actually need to be changed in the helper table and change the SQL to

UPDATE table1
SET column1 = ConversionHelpTable.NewValue
FROM table1
INNER JOIN ConversionHelpTable
ON ConversionHelpTable.OldValue = table1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Erland Sommarskog

2005-09-21, 3:24 am

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> In that case, you'll have to bite the bullet and use a list of update
> statements. One for each table. Do enclose all update statements in a
> transaction and include error handling, so that you don't end up with
> the new value in half of your tables changed and the old value in the
> other half. (Instead of enclosing all in a transaction, you could take a
> backup before the conversion starts and restore if something goes
> wrong).

Also, one more important thing here. Don't do:

BEGIN TRANSACTION
go
UPDATE tbl
SET ...

IF @@error <> 0
go
UPDATE nexttbl

That is, don't split up the job in several batches, because some errors
causes SQL Server to abort the batch and rollback the transaction. If you
have multiple batches, you could end up having run the latter half, while
the first was rolled back.

I would recommend that you put all this stuff in a stored procedure, rather
than just a script.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links