CODEWITHSUNDEEP
sql-server
Pavan Kumar
Pavan Kumar

Reputation: 422

How to migrate data from 2 different tables using one by one rows

I want to migrate data from the doctor and lawyer table to the user table.

Here's my doctor table:

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

Here is my lawyer table

Name     Age  City
---------------------
pavan    35   Delhi
Kishor   45   Delhi
pavan    55   Mumbai

I want to insert data from the doctor and lawyer table 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)

this is what my expected result should look like in the user table

Name     Age  Active
---------------------
pavan    27   1
Kishor   29   1
pavan    30   0
pavan    35   0
kishor   45   0
pavan    55   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.

Upvotes: 0

Views: 61

Answers (1)

Karan
Karan

Reputation: 12619

You can use CTE & ROW_NUMBER with condition to set active column value. Try like below.

;WITH AllUser AS (
    SELECT name, age FROM doctor
    UNION ALL
    SELECT name, age FROM lawyer
)
INSERT INTO User (name, age, active)
SELECT name, age, IIF(ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) = 1, 1, 0)
FROM AllUser

Upvotes: 1

Related Questions