如何减少tsql脚本中的插入次数？

用户名

我只需要使用一次插入即可填写用户表。表格中有4列：

FirstName    LastName   Password    Email

现在，我只能使用两个插入物来做到这一点。首先，输入FirstName和LastName，然后根据其名称生成密码和电子邮件。因此，在第一次插入后，我的“密码”列和“电子邮件”列填充了NULL。这就是为什么我需要从表中删除不必要的行的原因。
下面的代码：

DROP TABLE Users

CREATE TABLE Users(
    FirstName nvarchar(10) ,
    LastName nvarchar(10),
    Password nvarchar(128),
    Email nvarchar(30),
    )

INSERT INTO Users (FirstName, LastName) VALUES ('Adams','Armstrong'),
                                            ('John','Barnes'),
                                            ('Mark','Barnetty'),
                                            ('Mike','Ra'),
                                            ('Harry','Radley'),
                                            ('Nick','Kade'),
                                            ('Brian','Kael'),
                                            ('Lea','Fahim'),
                                            ('Julia','Dacey'),
                                            ('Anna','Octavio'),
                                            ('Kate','Yale')

INSERT INTO Users (FirstName, LastName, Password, Email) 
SELECT FirstName, LastName, Password, Email =
    CASE 
    WHEN Email = 0 THEN
    LOWER(LEFT(FirstName,1) + LastName) + '@hotmail.com'
    WHEN Email = 1 THEN
    LOWER(LEFT(FirstName,1) + LastName) + '@gmail.com'
    WHEN Email = 2 THEN
    LOWER(LEFT(FirstName,1) + LastName) + '@mail.ru'
    END
    FROM (SELECT  FirstName, LastName, REPLACE(LOWER(CONVERT(NVARCHAR(128), NEWID())), '-', '') as Password,
    CAST(RAND(CHECKSUM(NEWID())) * 3 as INT) as Email from Users) as random

DELETE FROM Users WHERE Password IS NULL and  Email IS NULL

SELECT * FROM Users

如何仅使用一次插入来填充“用户”表？
PS此表已经是临时的另一个。

杰特

这是CROSS APPLY无需CTE即可使用的干净解决方案：

INSERT INTO Users (FirstName, LastName, Password, Email)
SELECT 
    Names.FirstName, 
    Names.LastName, 
    REPLACE(LOWER(CONVERT(NVARCHAR(128), NEWID())), '-', ''),
    CASE 
        WHEN EmailInfo.EmailType = 0 THEN EmailInfo.EmailName + '@hotmail.com'
        WHEN EmailInfo.EmailType = 1 THEN EmailInfo.EmailName + '@gmail.com'
        WHEN EmailInfo.EmailType = 2 THEN EmailInfo.EmailName + '@mail.ru'
    END
FROM (
        VALUES 
        ('Adams','Armstrong'),
        ('John','Barnes'),
        ('Mark','Barnetty'),
        ('Mike','Ra'),
        ('Harry','Radley'),
        ('Nick','Kade'),
        ('Brian','Kael'),
        ('Lea','Fahim'),
        ('Julia','Dacey'),
        ('Anna','Octavio'),
        ('Kate','Yale')
    ) Names (FirstName, LastName)
    CROSS APPLY (
        SELECT 
            LOWER(LEFT(FirstName,1) + LastName) AS EmailName,
            CAST(RAND(CHECKSUM(NEWID())) * 3 as INT) AS EmailType
    ) EmailInfo

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。