c# - SQL - SQLDataAdapter Update causing primary key violation -
i need able change primary keys in table. problem is, of keys changing existing key values. e.g. record1.id 3=>4 , record2.id 4=>5. need keep these primary keys set foreign keys (which cascade update) there reasonable way accomplish this, or attempting sql heresey?
as why, have data 1 set of tables linked primary key getting inserted/updated set of structured tables. insertion in parts, part of deduping process, , if update of tables inserted new primary key, life easier.
one solution start indexing on destination table higher incoming tables row count ever reach (the incoming table gets re=indexed every time), i'd still know if possible above, otherwise.
tia
you attempting sql heresy. i'm pretty open-minded , know lot of times 1 must things seem crazy. annoys me when people arrogantly answer "you should differently", when have know idea situation is. must tell you should differently. heh heh.
no, there no way elegantly sql\dataadapter. could through ado.net series of t-sql commands. have to, every time, turn on identity-overwrite mode (set identity_insert thetable on
), query values on table incremented one, , turn of autonumber-overwrite mode. need increment other tables use foreign key. wait, gets worse:
- you need in transaction, because cannot have else happening these tables during time, , because if there failure need rollback. good-size chunk of processing; tables locked bit.
- if have foreign key constraints between these tables, need turn them off before this, , re-implement them afterwards.
Comments
Post a Comment