能否在执行SELECT * INTO #temp_table时为临时表添加主键？

Great question—let’s walk through this clearly, since your goal to keep the FactIncident table from ending up empty is critical for data integrity.

First, the short answer: No, you can’t directly add a primary key when using SELECT * INTO #temp_table. Here’s why:
The SELECT * INTO syntax creates a new table (temp or permanent) by copying column definitions (data types, lengths, nullability) from the source query, but it does not carry over constraints like primary keys—even if the source table has one. The resulting temp table will have no constraints by default.

But don’t worry, there are two straightforward ways to achieve your goal of validating primary key uniqueness in a temp table before touching your fact table:

Option 1: Define the temp table with a primary key first, then insert data #

This is the most controlled approach, especially since you know the schema of FactIncident:

-- Create temp table with explicit primary key matching FactIncident
CREATE TABLE #temp_table (
    IncidentID INT PRIMARY KEY, -- Replace with your actual primary key column(s)
    IncidentDate DATETIME,
    Category VARCHAR(50),
    -- Add all other columns matching Source_table/FactIncident
)

-- Try inserting source data into temp table
INSERT INTO #temp_table
SELECT * FROM Source_table

If the source data has duplicate primary keys, this INSERT will fail immediately—before you ever touch the FactIncident table. That way, your fact table stays intact until you’re sure the temp data is valid.

Option 2: Create the temp table first, then add the primary key constraint #

If you don’t want to manually define all columns (e.g., if the schema changes often), you can use SELECT * INTO to create the temp table, then add the primary key afterward. Just note this will fail if duplicates already exist in the temp data:

-- Create temp table from source data
SELECT * INTO #temp_table FROM Source_table

-- Attempt to add primary key constraint
ALTER TABLE #temp_table
ADD CONSTRAINT PK_temp_Incident PRIMARY KEY (IncidentID) -- Replace with your PK column

If duplicates exist, the ALTER TABLE will throw an error. To fix this, add a deduplication step first:

-- Create temp table with only distinct rows (adjust to keep the correct duplicate if needed)
SELECT DISTINCT * INTO #temp_table FROM Source_table

-- Now add the primary key
ALTER TABLE #temp_table
ADD CONSTRAINT PK_temp_Incident PRIMARY KEY (IncidentID)

Key Takeaway for Your Workflow #

Whichever option you choose, stick to this critical order of operations:

1. Validate source data in the temp table (ensure no primary key conflicts)
2. Only if the temp table is valid, truncate FactIncident and insert from the temp table

This way, you never risk truncating your fact table only to have the insert fail—keeping your production data safe.

内容的提问来源于stack exchange，提问作者Shiva Kumar