Update/Append Transact Loop

[michaelfaulkner]

Two tables with SS# as the primary key in both.

Want to relate them, and if the SS#'s match, update the data from table B into table A. If the SS#'s don't match, append the data from table B into table A.

Not a programmer, so appreciate any help.

 

[SQLSister]

First, you do not want to do this in a loop as loops are inefficent and should never be used for inserts and updates. Second, SSN is a poor choice for a primary key as people can change their SSN and it is considered private information and thus should not be accessible to all.

Now as to how to do what you want. Basically you want to run a set-based update statement, then a set-based insert statment.

something like:

Update tableA
Set field1 = b.field1,
field2 = b.field2
from tableA join tableB b
on tableA.ssn = b.ssn

Insert into tableA (ssn, field1, field2, Field3)
Select b.ssn, b.field1, b.field2, b.field3 from tableb b
left join tableA a on b.ssn = a.ssn
where a.ssn is null

Questions about posting. See faq183-874

 

[michaelfaulkner]

Thanks very much! I did a wee bit of programming with dBase many eons ago. They were loops.

We protect SSN's, but must have them in order to process student data.