sql - SQL Auto-populate ID column based on another column

Question

I have a workflow where source table is used to populate the destination table.
I have tried to simulate this workflow in the code below.

-- creating/populating source table
CREATE TABLE #SourceTable
(
     CampaignName VARCHAR(50),
     CustomerNumber INT
)

INSERT INTO #SourceTable 
VALUES ('Campaign1', 1111), ('Campaign1', 2222), ('Campaign1', 3333),
       ('Campaign2', 4444), ('Campaign2', 2222), ('Campaign2', 1111)

-- create/populate destination table
CREATE TABLE #DestinationTable
(
    CampaignID INT,
    CampaignName VARCHAR(50),
    CustomerNumber INT
)

-- Simulating populating the #DestinationTable
INSERT INTO #DestinationTable (CampaignName, CustomerNumber)
    SELECT CampaignName, CustomerNumber
    FROM #SourceTable

The source table will get created in some way, but then it is used to populate the destination table in the same way as my sample code.

The destination table is at CustomerNumber level. I want to autopopulate an ID field (without the user having to code it in) that will give a new number at CampaignName level.

So for example, I want the output of the #DestinationTable to be:

CampaignID  CampaignName    CustomerNumber
------------------------------------------
1           Campaign1         1111
1           Campaign1         2222
1           Campaign1         3333
2           Campaign2         4444
2           Campaign2         2222
2           Campaign2         1111

But I need the CampaignID column to be auto-populated whenever new rows are being inserted, like an IDENTITY column, but instead of giving each row a number, I need it to give each CampaignName a new number.

Is that possible?

Thanks

标签： sqlsql-serveruniqueidentifier

Answer 1

这可以使用dense_rank().

SELECT dense_rank() over (order by CampaignName) as rn, CampaignName, CustomerNumber
FROM #SourceTable

要验证您的customer number姓名是否campaign已经存在于您的目的地，请使用not exists关键字。

SELECT dense_rank() over (order by t1.CampaignName) as rn, t1.CampaignName, t1.CustomerNumber
FROM #SourceTable t1
WHERE not exists (select 1 from #DestinationTable t2
    where t2.CustomerNumber = t1.CustomerNumber and t2.CampaignName = t1.CampaignName)