I think what you need is a migration plan written out.
Lay out all of your FK and Check Constraints, and dependencies and hierarchies
For example
Employees have dependency on state
Counties have dependency on state
=> Populate State First
=>Then Populate Employees
=> Then Populate Counties
With a loop and check error you will grow a full white beard by the time it finishes.
Instead of checking state does not exist (AZ) and skipping and logging, you create state so it does exist (AZ) before populating employees for that state (AZ) no muss no fuss.
insert into **databaseb**.dbo.states
select distinct state
from **databasea**.dbo.employees e
where not exists(select 1 from states tgt where e.state = tgt.statename)
Then when you go to insert all employees
Here are my check constraints
| dbo.Employee | Age | ([Age]<(21)) |
|---|---|---|
| dbo.Employee | Salary | ([Salary]>(0)) |
insert into **databaseb**.dbo.employees
select id, name, s.stateid
from **databasea**.dbo.employees e
join states s on e.state = s.statename
where Salary > 0 and Age < 21
preemptive approach