September 8, 2008 at 2:42 pm
I have a production table called 'County2' with the following columns:
CountyID (identity),
[Name],
StateID,
Elevation
I need to update any changes to [name], StateID, and Elevation from another table called County2_Staging.
The CountyID column has been used as the primary key. I'm a little concerned about using this to update records from the staging table to County2.
I know that I could identify the changed records from staging like this:
select ct.[name], ct.StateID, ct.Elevation from county2_staging ct
left outer join county2 cs
on ct.[name] = cs.[name]
and ct.StateID = cs.StateID
and ct.Elevation = cs.Elevation
where cs.[name] is null
Is there a way that I can update county2 without using the identity column?
Thank you for your help
CSDunn
September 8, 2008 at 3:22 pm
Is there a unique index on the table?
September 8, 2008 at 3:30 pm
There is not, but in the particular case of County/State, No U.S. State has two counties by the same name. I think the update statement would have to be something like this:
UPDATE County2
SET Elevation = cs.Elevation
FROM County2 ct, County2_Staging cs
WHERE ct.[name] = cs.[name]
AND ct.StateID = cs.StateID
I guess we would have to assume that a given County and State would remain the same, and that any change would be inserted as a new record.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply