SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Accounts(AccountCode NVARCHAR(10), FullName NVARCHAR(100), DateCreated DATETIME)
CREATE TABLE Users(FirstName NVARCHAR(100), LastName NVARCHAR(100),
UserId INT, AccountCode NVARCHAR(100) DEFAULT 'Basic')
INSERT INTO Users(FirstName, LastName, UserId)
VALUES ('Alex', 'Kamal',1), ('Naomi', 'Nagata',2), ('Amos', 'Burton',3)
3 rows affected

SELECT * FROM Users;


FirstName
LastName
UserId
AccountCode


Alex
Kamal
1
Basic

Naomi
Nagata
2
Basic

Amos
Burton
3
Basic


CREATE TABLE #temp(AccountCode NVARCHAR(100), UserId INT);

MERGE INTO Accounts trg
USING (Select  AccountCode = CONCAT('PMT', ROW_NUMBER() OVER(ORDER BY 1/0))
          ,FullName = u.FIRSTNAME + ' ' + u.LASTNAME
          ,DateCreated = GETDATE()
          ,u.UserId
        From   Users u
        where AccountCode = 'Basic') src
ON trg.AccountCode = src.AccountCode
WHEN NOT MATCHED THEN 
   INSERT (AccountCode, FullName, DateCreated)
   VALUES (src.AccountCode, src.FullName, src.DateCreated)
OUTPUT inserted.AccountCode, src.UserId
INTO #temp;

Update u
set AccountCode = t.AccountCode
FROM users u
JOIN #temp t
  ON u.UserId = t.UserId;
6 rows affected

SELECT * FROM Users;


FirstName
LastName
UserId
AccountCode


Alex
Kamal
1
PMT1

Naomi
Nagata
2
PMT2

Amos
Burton
3
PMT3


SELECT * FROM Accounts


AccountCode
FullName
DateCreated


PMT1
Alex Kamal
01/11/2018 19:53:30

PMT2
Naomi Nagata
01/11/2018 19:53:30

PMT3
Amos Burton
01/11/2018 19:53:30

AccountCode	FullName	DateCreated
PMT1	Alex Kamal	01/11/2018 19:53:30
PMT2	Naomi Nagata	01/11/2018 19:53:30
PMT3	Amos Burton	01/11/2018 19:53:30