CODEWITHSUNDEEP
sqlsql-servert-sql
DalhousieDuck
DalhousieDuck

Reputation: 339

Assign values from a table variable to each row of a table?

I have a table (Reference table) that will hold unique values (uniqueidentifer types). I have two million users and I need to generate a unique value for each one in this Reference table. As I create them, I would like to capture these unique values and assign them to each user in another table, called a User table.

Below is my attempt: I have created a table variable called @ReferenceIds. How can I take @ReferenceIds and assign each unique value to a separate row in the User table?

create procedure create_reference_ids
    @numberOfNewAccounts int
    DECLARE @ReferenceIds TABLE (Id uniqueidentifier)
    set @numberOfNewAccounts = 2000000
as
begin
    while @numberOfNewAccounts > 0
    begin
        insert into Reference (0,0,@UtcNow,@UtcNow)
        OUTPUT Inserted.Id INTO @ReferenceIds
        set @numberOfNewAccounts = @numberOfNewAccounts - 1
    end
end

exec create_reference_ids

Upvotes: 0

Views: 970

Answers (1)

Dale K
Dale K

Reputation: 27226

Use a merge statement to insert the exact number of reference values as you have users, and output the new Ids into a table variable which links your new reference ids to your existing user ids. Then carry out an update on your users table.

DECLARE @NewId TABLE (UserId int, ReferenceId int);

-- Using Merge for insert is the only way to get values from columns in the output table which don't exist in the Target table
MERGE INTO dbo.Reference AS Target
USING dbo.[User] AS Source
ON 1 = 0 -- Force an insert regardless
WHEN NOT MATCHED THEN
    -- Best practice is to always explicitly name your columns
    INSERT (Col1, Col2, Col3, Col4)
    VALUES (0, 0, @UtcNow, @UtcNow)
    OUTPUT Source.Id, Inserted.Id
    INTO @NewId (UserId, ReferenceId);

UPDATE U SET
  ReferenceId = I.ReferenceId
FROM dbo.[User] U
INNER JOIN @NewId I on I.UserId = U.Id;

Upvotes: 2

Related Questions