T-SQLデータをランダムに割り当てる例

エンジニアリングの実践では、テストのためにランダムにいくつかのデータを割り当ててデータベースに挿入することがよくあります.ここでは、乱数を用いてデータを割り当てる例を示します.
この例では、従業員情報を特定の部門の職名(title)にランダムに割り当てます.

DECLARE @Stuffs TABLE (StuffName VARCHAR(50));-- 

INSERT INTO @Stuffs -- 
VALUES ('Stuff1')
    ,('Stuff2')
    ,('Stuff3')
    ,('Stuff4');

DECLARE @StuffClasses TABLE ( -- ， ， 。
    Id INT IDENTITY
    ,Department VARCHAR(50)
    ,Title VARCHAR(50)
    )

INSERT INTO @StuffTitles ( -- ， Id
    Department
    ,Title
    )
SELECT D.NAME AS Department
    ,T.NAME AS Title
FROM [dbo].[Titles] T
INNER JOIN ClientAccount..Departments D ON T.DepartmentsId = D.id

DECLARE @TitleCount INT;

SELECT @TitleCount = COUNT(*) -- 
FROM @StuffTitles

DECLARE @StuffMappings TABLE (
    StuffName VARCHAR(50)
    ,TempId INT
    )

INSERT INTO @StuffMappings (
    StuffName
    ,TempId
    )
SELECT StuffName
    ,(CAST((rand(checksum(newid())) * @TitleCount) AS INT) + 1) AS TempId -- 
FROM @Stuffs

MERGE INTO [dbo].StuffTitleMappings AS D -- 
USING (
    SELECT I.StuffName
        ,ST.Department AS DepartmentName
        ,ST.Title AS Title
        ,GETDATE() AS DateUpdated
    FROM @StuffTitles ST
    INNER JOIN @StuffMappings SM ON ST.Id = SM.TempId
    ) AS S
    ON D.StuffName = S.StuffName
WHEN MATCHED
    THEN
        UPDATE
        SET DepartmentName = S.DepartmentName
            ,D.Title = S.Title
            ,D.DateUpdated = S.DateUpdated
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            StuffName
            ,DepartmentName
            ,Title
            ,DateUpdated
            )
        VALUES (
            S.StuffName
            ,S.DepartmentName
            ,S.Title
            ,S.DateUpdated
            );
GO