如何向临时表插入序列号唯一的行？原表无主键需新增主键

Alright, let's fix this duplicate serial number problem and get your temp table set up with a proper primary key. I see you've got hundreds of thousands of messy records with duplicates, no PK on the original table, and you need to pull only unique serials into a temp table. Here's how to do it right, with a couple of options depending on your needs:

Step 1: Create the Temp Table with a Primary Key #

First, we'll define the temp table with an auto-incrementing primary key (since your original table doesn't have one) and include the serial number column (plus any other columns you need to retain):

-- Create temp table with auto-incrementing primary key
CREATE TABLE #UniqueSerialRecords (
    TempRecordID INT IDENTITY(1,1) PRIMARY KEY, -- New PK column
    SerialNumber VARCHAR(100), -- Adjust data type to match your original table
    -- Add other columns from your original table here if needed
);

Step 2: Insert Unique Records (Two Common Approaches) #

Option 1: Use DISTINCT for Full-Row Uniqueness #

If you want to keep all records where the entire row is unique (not just the serial number), use DISTINCT to filter duplicates during insertion:

-- Insert only unique rows (based on all selected columns)
INSERT INTO #UniqueSerialRecords (SerialNumber /*, other columns */)
SELECT DISTINCT SerialNumber /*, other columns */
FROM YourOriginalTableName; -- Replace with your actual table name

Option 2: Keep One Record Per Serial Number #

If you only need one entry per serial number (regardless of other column values), use ROW_NUMBER() to rank records per serial and pick just the first one:

-- Insert one record per unique serial number (adjust ORDER BY to pick specific rows)
INSERT INTO #UniqueSerialRecords (SerialNumber /*, other columns */)
SELECT SerialNumber /*, other columns */
FROM (
    SELECT 
        SerialNumber,
        -- Include other columns here
        ROW_NUMBER() OVER (
            PARTITION BY SerialNumber 
            ORDER BY (SELECT NULL) -- Use a real column (e.g., CreateDate) to pick a specific row
        ) AS RowRank
    FROM YourOriginalTableName
) AS RankedRecords
WHERE RowRank = 1;

Note: Replace ORDER BY (SELECT NULL) with a meaningful column (like a creation date) if you want to retain the oldest/newest record for each serial instead of a random one.

Bonus: Batch Insert for Extra Large Datasets #

If you're dealing with hundreds of thousands of records, inserting in batches can prevent long-running transactions and lock contention. Here's how to adapt your existing loop logic for this:

DECLARE @totalUnique INT = (SELECT COUNT(DISTINCT SerialNumber) FROM YourOriginalTableName);
DECLARE @currentBatchStart INT = 1;
DECLARE @batchSize INT = 10000; -- Adjust batch size based on your server's capacity

WHILE @currentBatchStart <= @totalUnique
BEGIN
    INSERT INTO #UniqueSerialRecords (SerialNumber /*, other columns */)
    SELECT SerialNumber /*, other columns */
    FROM (
        SELECT 
            SerialNumber,
            -- Include other columns
            ROW_NUMBER() OVER (ORDER BY SerialNumber) AS GlobalRowNum
        FROM (
            SELECT DISTINCT SerialNumber /*, other columns */
            FROM YourOriginalTableName
        ) AS UniqueSerials
    ) AS BatchData
    WHERE GlobalRowNum BETWEEN @currentBatchStart AND @currentBatchStart + @batchSize - 1;

    SET @currentBatchStart = @currentBatchStart + @batchSize;
END

This way, you're inserting records in chunks instead of all at once, which is easier on your database.

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