Sql - How To Insert Values While Checking Condition Based On Another Table? If Null Generate New Id

I was given a task to use cursor to duplicate tables. (Don't ask me why or suggest me to use other way cause that's part of the requirement of the task given) SET ANSI_NULLS ON GO

Solution 1:

Same case as in prior step - put them into single cursor. First - copy Person and obtain new personid, second - insert Person_Link with link to newly created person.

If you need to create another Person_Link (which is strange because you just copied all of Person_Links) to some "primary" person - you need new Person_Id.

(SELECT@PersonIDFROM Company 
WHERE Comp_PrimaryPersonId isnotnullAND Comp_CompanyId =@Comp_CompanyId)

In this select you refer old @Comp_CompanyId so you could obtain only old Person_Id while you are needing exactly new one (which can be found under @companyId id).

But you will not obtain even old id with this select - because it selects constant value of @PersonID variable which is not even initialized before this select thus contains some garbage from previous places of usage.

Anyway, you already lost relation with primary person for new company at that moment because of the very first insert into Company - its newly generated Comp_PrimaryPersonId value links to nothing.

One way to solve it is to check inside of cursor whether @c_PeLi_PersonId is same as old companies'Comp_PrimaryPersonId and to update new companies'Comp_PrimaryPersonId field with @PersonId value. Store old companies' Comp_PrimaryPersonId into variable at the beginning of your script and use it in cursor.