Loop through a table and assign foreign key to new record if null

Question

I am working on an SQL Database

I have a Table Users

 Id   Name   Age    AddressId
----+------+------+-----------

Where AddressId is a foreign key to a table names Addresses

The Addresses Table:

 Id   Country   State  City   ZipCode
----+---------+------+------+---------

I need an SQL Script, that loops through all the users and If the AddressId of that user is null, Create a new record in the Addresses Table with default Values and assign it to the user as a foreign key

Something like this in SQL script:

Foreach (User in Users)
    If(user.AddressId==null)
        INSERT INTO Addresses values('DefaultCountry', 'DefaultState', 'DefaultCity', 99999)
        User.AddressId= the id of the newly created row

EDIT It is a one-to-one relationship

thanks for any help

Answer 1

You can use merge and output to do this without a loop.

declare @IDs table(UserId int, AddressId int);

merge Addresses as T
using (select Id, 'DefaultCountry', 'DefaultState', 'DefaultCity', 99999
       from Users
       where AddressID is null) as S (UserId, Country, State, City, ZipCode)
on 0 = 1
when not matched then
  insert(Country, State, City, ZipCode) 
    values (S.Country, S.State, S.City, S.ZipCode)
output S.UserId, inserted.Id into @IDs;

update Users
set AddressId = IDs.AddressID
from @IDs as IDs
where Users.Id = IDs.UserId;

SE-Data

If you only need to add one address and connect that one to all users that have none use this instead.

declare @AddressId int;

insert into Addresses(Country, State, City, ZipCode)
  values('DefaultCountry', 'DefaultState', 'DefaultCity', 99999);

set @AddressId = scope_identity();

update Users
set AddressId = @AddressId
where AddressId is null;