Using MS SQL Server 2000 I am trying to update muliple records in one
table with multiple records from another table.

Table 1: TimeEstimates (TimeEstimateID, HoursEstimated) 
Table 2: EmployeeAssignments (EmployeeAssignmentsID, TimeEstimateID,
HoursAssigned)
Table 3: TimeCard (TimeCardID, EmployeeAssignmentID, HoursWorked) 

The situation is that I need to update multiple records in the
EmployeeAssignments table with multiple records from the TimeEstimates
table.

For example I have the following TimeEstimateIDs in the
EmployeeAssignments table

1290 
1290 
1291 
1291 
1294 

these are all unique assignments that are tied back to the
TimeEstimate table.

I then create 5 new TimeEstimate records 

1301 
1302 
1303 
1304 
1305 

Now I want to update the old TimeEstimateIDs in the
EmployeeAssignments table (1290, 1291, etc...) with the new
TimeEstimate records (1301, 1032 etc...)

There is no direct relationship between the newly created TimeEstimate
records and the TimeEstimate original records =( although I can
populate 2 different temporary table with both sets of data... I just
need a way to syncronize the 2.

I've tried using a cursor but it seems as if I need 2 seperate cursors
each looping independantly with one of them updating data in the
other. Can you smell the smoke coming out of my ears?

---

Request for Question Clarification by molloch-ga on 29 May 2002 16:40 PDT

Hi,

This can't be done without a redesign of your tables. The reason being
that as you have said "There is no direct relationship between the
newly created TimeEstimate records and the TimeEstimate original
records...". Therefore you have no way of knowing which
TimeEstimateID's Related to which EmployeeAssignmentID's and are not
able to update the Assingment table based on new records you created
in the TimeEstimate table. If you are in a position where you can add
fields to your tables let me know and I will redesign your tables for
you with the appropriate fields to make this work.

Thanks

Molloch

Hi Again,

I have been through this with some Oracle DBA's and we have come to
the following conclusion:

Given the tables above it is not possible to perform the query. There
are a couple of ways you can address the problem, but the simplest way
which will have the least impact on your database is to create a
"changemap" table with both sets of data in it. This could be a
temporary table and needs to have two columns which map the old
TimeEstimateID to a new TimeEstimateID.

So createtable :

CHANGEMAP
---------
OLDTimeEstimateID (int)
NEWTimeEStimateID (int)

Index both columns. 

When you create your new time estimates, you need to populate this
table with the old TimeEstimateID and the TimeEstimateID you just
created (New) in their respective columns. This will provide the
mapping between your TimeEstimates and allow you to perform the query.

The query needs to be a nested query, which updates the
EmployeeAssingment table with the new ID's. It looks like this:

UPDATE SET EmployeeAssignment.TimeEstimateID =
    (SELECT NEWTimeEstimateID
     FROM CHANGEMAP
     WHERE OLDTimeEstimateID = TimeEstimateID)
WHERE TimeEstimateID In
    (SELECT OLDTimeEstimateID
     FROM CHANGEMAP)

The reason the "In" part of the second WHERE clause is necessary; is
that SQL server will return null and set your New TimeEstimateID to
null for any records which do not have an "OLD" ID in the ChangeMap
table. This isn't the fastest of ways to do this, to speed up the
query make sure you have both columns of changemap indexed. There are
other ways to perform the second nested query that may be faster, such
as using conditional programming within the query to make sure the
returned value is not null. This would depend on the size of the
recordsets you wished to change.

The other thing you can do is to make sure that there is ALWAYS a
record for every OLD ID in the ChangeMap table, even if it isn't going
to change(ie NEWID = OLDID). If you wanted to do this just remove the
statement after (and including) the second WHERE.

You need to clear the temporary table before each multiple record
update.

The method you hinted at using the 2 cursors is also possiblebut the
coding would be much more complex and it is unlikely to gain much
speed advantage over the method above for small(ish) recordsets. If
you have more than 1000 TimeEstimates to change then the cursor method
may be more effective. Otherwise stick to the method above.

Let me know if you need anything clarified.

Molloch.

---

Request for Answer Clarification by djchrome-ga on 30 May 2002 09:44 PDT

Thanks, that is sort of the path I've been heading down, except I
can't figure out how to populate the ChangeMap table with the new and
old numbers side by side (does that make sense)?

I can easily generate 2 seperate select statements 1 pulling the
oldIDs, and another pulling the newIDs... but how can I get them side
by side in 1 table?

For example, using the example data in the original question I need
the ChangeMap table to end up looking like:

OLDID |  NEWID
==============
1290  |  1301
1290  |  1302
1291  |  1303
1291  |  1304
1294  |  1305

How can I accomplish this? Then from that point I can use the update
method you described. If it makes the problem any easier I can
guarantee that I will always have the same number of new records, as
the number of old records.

I am guessing that in the real world application the number of records
being updated will probably be in the 5 to 30 range. Rarely exceeding
100.

Thanks!

---

Clarification of Answer by molloch-ga on 30 May 2002 18:04 PDT

Hi,

This is a risky business. If you have no way to identify the new and
old ID's from your code, you will have poor referential integrity and
may insert ID's that break your program. The single best way to
acheive the CHANGEMAP table is to have you application create the
table through scripting using ado or similar:

ado.Execute "INSERT INTO CHANGEMAP (OLDTimeEstimateID,
NEWTimeEstimateID) VALUES (" & varOLDTimeEstimateID & ", " &
varNEWTimeEstimateID & ");"

However, if you dont have access to the code, or are unable to create
records from your application, you don't have many choices. For the
following to work, you need to be completely sure that the two lists
of ID's are written to the database in the correct order. If you get
the order out, you will end up with mismatched records.

1/ Create two temporary tables. OLDID and NEWID. Each table needs two
fields, ID and TimeEstimateID. The type of the ID column must be INT
IDENTITY. Add your lists of IDs to the respective columns.

2/ You should now have 2 tables which look like this:

OLDID                       NEWID
--------                    ----------
1|1290                      1|1300
2|1291                      2|1301
3|1292                      3|1302
4|1293                      4|1303

3/ Now you have 2 tables and need to make the CHANGEMAP table with
this query:

SELECT OLD as OLDTimeEstimateID, NEW as NewTimeEstimateID INTO
CHANGEMAP
FROM OLDID INNER JOIN NEWID ON OLDID.ID = NEWID.ID

This will give you your list of ID's. This is far from ideal though
and you should make every effort make the applicatin spit out the
changemap table. You have to be very careful that your application
doesn't try and insert a record into either table and fail, as this
will use the Identity ID and the target table will skip an ID,
throwing everything out...

ie:
1 = 2902
2 = 2903
3 = ERROR
4 = 2904 (Which was meant to go into 3)

You should destroy and recreate the NEWID and OLDID tables each time
to make sure your identity seed is reset to 1.

Good luck, Let me know if you need any further clarification.

Thanks

Molloch

---

Request for Answer Clarification by djchrome-ga on 03 Jun 2002 10:57 PDT

Can you show me how to accomplish this using cursors? I was told that
creating tables is one of the most expensive processes in a database,
and I want to keep my code as light as possible. Just a real quick
explination, then I'll stop bugging you =)

Thanks Guru Molloch,
-Jason

---

Clarification of Answer by molloch-ga on 13 Jun 2002 20:11 PDT

Hi again,

Sorry for the delay in answering this but have been computer-less for
last week.

Creating and destroying tables is slow, but not so much that it will
effect your database performance noticably. I can help you using
cursors but the methods are far more involved. Can you give me an
outline of your program, the methods used to access the database and
the language you are programming in and I will see what I can do for
you.

Thanks again.

Michael