sql server - Assigining primary key column value to foreign key column -

i have 2 tables called visitormaster , supportvisitor. in visitormaster have column called visitorid1 primary key (but not identity column). in supportvisitor have column called visitorid2 acts foreign key visitormaser table.but requirement want last generated column value of visitorid1 inserted in visitorid2 of supportvisitor table. how achieve this.. please help??

i'm thinking best bet ensure wrap insert visitormaster table transaction , within transaction obtain value of visitorid using data you've inserted query , putting supportvisitor before either committing or rolling lot.

you use trigger triggers nasty things can have kinds of unintended consequences since operate more or less invisibly i'd steer clear of them.

ultimately though i'd ask why have got non-identity surrogate pk in 1 column? it's pretty bad bit of design. either use natural key (combination of forename, surname, timestamp or whatever makes natural unique key) or use auto-incrementing identity field surrogate pk (whereupon can use scope_identity) because otherwise key pretty shaky , not guaranteed unique. how generating value? held in separate table (i've known databases use kind of system, eav model databases, , not system in way shape or form , isn't easier deal arbitrary values)? if have influence on design @ should change because smells , cause many, many more problems in future.