INSERT INTO new table setting Primary Key

Question

I have a SQL command that converts from seconds to HH:MM:SS. It creates new tables as it gets the seconds from the original table. Is there a way to set the primary key of the name column when it creates the new tables? Here is the SQL I am using:

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

I know I could make another alter table command that will set it for me like this:

ALTER TABLE main
ADD PRIMARY KEY (Name)

I was hoping there was a way to have it set the PK as soon as it's created, is this possible?

Answer 1

Before inserting data try to create new table variable with PRIMARY KEY set. Try below code

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 

Answer 2

There is no way to create primary key constraint automatically. You either:

• explicitly create table with PRIMARY KEY defined and have a n INSERT ... INTO

OR

• use SELECT INTO table and alter it by adding the PK constraint (ALTER TABLE ... ADD CONSTRAINT PK_tablename PRIMARY KEY (col_name))

Tip: try to always use named constraints, as it is much easier to change (drop) them if name is fixed (not server generated)