sql-server - INSERT INTO 新表设置主键

Question

我有一个从秒转换为 HH:MM:SS 的 SQL 命令。它在从原始表中获取秒数时创建新表。有没有办法在创建新表时设置名称列的主键？这是我正在使用的 SQL:

SELECT 
    main.[Name], 
    RIGHT('0' + CAST(main.[Staffed Time] / 3600 AS VARCHAR), 2) + ':' +
       RIGHT('0' + CAST((main.[Staffed Time] / 60) % 60 AS VARCHAR), 2)  + ':' +
       RIGHT('0' + CAST(main.[Staffed Time] % 60 AS VARCHAR), 2) AS StaffedTime, 
    RIGHT('0' + CAST(main.[Time in Break] / 3600 AS VARCHAR), 2) + ':' +
       RIGHT('0' + CAST((main.[Time in Break] / 60) % 60 AS VARCHAR), 2)  + ':' +
       RIGHT('0' + CAST(main.[Time in Break] % 60 AS VARCHAR), 2) AS BreakTime, 
    RIGHT('0' + CAST(main.[Time in Lunch] / 3600 AS VARCHAR), 2) + ':' +
       RIGHT('0' + CAST((main.[Time in Lunch] / 60) % 60 AS VARCHAR), 2)  + ':' +
       RIGHT('0' + CAST(main.[Time in Lunch] % 60 AS VARCHAR), 2) AS LunchTime
INTO
    DailyReport
FROM   
    main

我知道我可以创建另一个 alter table 命令来像这样为我设置它:

ALTER TABLE main
ADD PRIMARY KEY (Name)

我希望有一种方法可以让它在创建后立即设置 PK，这可能吗？

Answer 1

在插入数据之前，尝试创建带有 PRIMARY KEY 集的新表变量。试试下面的代码

CREATE TABLE DailyReport (
                     ID INT Primary Key IDENTITY(1,1),
                     [Name] NVARCHAR(30) ,
                     StaffedTime NVARCHAR(30), 
                     BreakTime NVARCHAR(30),
                     LunchTime NVARCHAR(30)
                    )

INSERT INTO DailyReport
SELECT main.[Name], 
 RIGHT('0' + CAST(main.[Staffed Time] / 3600 AS VARCHAR),2) + ':' +
 RIGHT('0' + CAST((main.[Staffed Time] / 60) % 60 AS VARCHAR),2)  + ':' +
 RIGHT('0' + CAST(main.[Staffed Time] % 60 AS VARCHAR),2)
 AS StaffedTime, 
 RIGHT('0' + CAST(main.[Time in Break] / 3600 AS VARCHAR),2) + ':' +
 RIGHT('0' + CAST((main.[Time in Break] / 60) % 60 AS VARCHAR),2)  + ':' +
 RIGHT('0' + CAST(main.[Time in Break] % 60 AS VARCHAR),2) 
 AS BreakTime, 
 RIGHT('0' + CAST(main.[Time in Lunch] / 3600 AS VARCHAR),2) + ':' +
 RIGHT('0' + CAST((main.[Time in Lunch] / 60) % 60 AS VARCHAR),2)  + ':' +
 RIGHT('0' + CAST(main.[Time in Lunch] % 60 AS VARCHAR),2) 
 AS LunchTime
FROM main

SELECT * FROM DailyReport