How to copy data from one table to another using one by one row

August 4, 2022

I’m trying to copy the data from the Player table to the User table.

Here’s my Player table:

Name     Age  City
---------------------
pavan    27   Delhi
Kishor   29   Delhi
pavan    30   Delhi

I want to insert this data into the User table which has these columns:

Name    Age    Active

Now I want that there should be only one active name.

Rest all duplicate entries should be marked inactive (active = 0)

I tried this query, but it didn’t work:

INSERT INTO User (name, age, active)
    SELECT 
        name, age,
        CASE 
            WHEN EXISTS(SELECT 1 FROM User u WHERE u.name = name) 
                THEN 0 
                ELSE 1 
        END

Thanks in advance.

>Solution :

I would use a CTE here first to make the selection of which name record is set to active more deterministic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT NULL)) rn
    FROM Player
)

INSERT INTO [User] (name, age, active)
SELECT name, age, CASE WHEN rn = 1 THEN 1 ELSE 0 END
FROM cte;