I have two tables that are related. The first table (Sites) has a varchar(4)
for a primary key and I want to change it to be an int identity column. I
broke the relationships and renamed the current pk column to siteID_Old. Then
created a new column called siteID and made it int identity. Now I need to
update the other table (Location) which has an fk for siteID and change the
values so they correspond to the new values I just created in the Sites table.
I'm not really sure how to proceed.
Do I rename the siteID column in the Locations table to siteId_Old and
create a new column called siteID and then perform an update?

I tried creating a stored procedure from an MS Access project but it only
allows one table in the window when I changed it to an Update Query so I
don't know how to reference the Sites table to perform the update. I am
taking a stab at the TSql statement but not sure exactly how it should be
written. I am using a copy of the database in case I really screw it up. Any
Ideas?
Thanks.

Rename the old foreign key in the other table to siteid_old as well,
add a new siteid field that's an INT field.

Then run SQL similar to the following...

UPDATE
L
SET
L.SiteID= s.SiteID
FROM
Location L
INNER JOIN Sites s ON L.SiteID_old = s.SiteID_old

-Eric Isaacs