a_m0d

    IF OBJECT_ID('dbo.dogs', 'U') IS NOT NULL
        DROP TABLE dbo.dogs
    GO
    
    IF OBJECT_ID('dbo.doghouses', 'U') IS NOT NULL
        DROP TABLE dbo.doghouses
    GO
    
    CREATE TABLE dogs (
        id INTEGER IDENTITY (1, 1) PRIMARY KEY
       ,name VARCHAR(MAX) NOT NULL
    );
    
    -- Now we want to give each dog a doghouse:
    CREATE TABLE doghouses (
        id INTEGER IDENTITY (1, 1) PRIMARY KEY
       ,name VARCHAR(MAX) NOT NULL
    );
    GO
    
    ALTER TABLE dogs ADD doghouse_id INTEGER REFERENCES doghouses (id);
    GO
    
    INSERT INTO dogs (name)
    VALUES
        ('Sparky'),
        ('Spot')
    ;
    
    DECLARE @DogsAndHouses TABLE (
        doghouse_id INT
       ,name VARCHAR(MAX)
    );
    
    INSERT INTO dbo.doghouses (name)
    OUTPUT INSERTED.id, INSERTED.name INTO @DogsAndHouses
    SELECT
        d.name
    FROM
        dbo.dogs d;
    
    UPDATE d
    SET
        doghouse_id = dah.doghouse_id
    FROM
        dogs d
        JOIN @DogsAndHouses dah ON d.name = dah.name;
    GO