SQL query for inserting rows into table if not exist

Meghana M

2y919SQL

Need to read from the employee table and insert into Location table only 1 row if column is having duplicate value

Eg:Employee table

ABC ,1234 , Pavithra p

ABC , 4567, Madhu

BCD ,8910 ,Pavithra Shiva

BCD ,111213, Chaitra

ABCEF , 124567,Priya

Excepted output: Location table

ABC ,1234 , Pavithra p

BCD ,8910 ,Pavithra Shiva

ABCEF , 124567,Priya

How to add this condition in an existing query

merge into Location as loc using (select e.Id, e.No, ln.Name from Employee e
inner join Locationname as ln on e.locationcode=ln.LocationId and
e.emploc=ln.LocId where e.Id is not null ) as emp on loc.id=emp.id
when not matched then
INSERT (Id,No,Name) values(emp.id, emp.no, emp.name);

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana2y ago

WITH RankedEmployee AS (
    SELECT
        Id,
        No,
        Name,
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT NULL)) AS RowNum
    FROM Employee
)
MERGE INTO Location AS loc
USING RankedEmployee AS emp
ON loc.Id = emp.Id AND emp.RowNum = 1
WHEN NOT MATCHED THEN
    INSERT (Id, No, Name) VALUES (emp.Id, emp.No, emp.Name);

You can try below query.

Thanks

Naimish

Jayraj Chhaya2y ago
To add the condition for inserting rows into the Location table only if they don't already exist, you can modify the existing query using the MERGE statement. The MERGE statement allows you to perform insert, update, and delete operations in a single statement based on a specified condition.

Here's the modified query:
```
MERGE INTO Location AS loc
USING (
    SELECT e.Id, e.No, ln.Name
    FROM Employee e
    INNER JOIN Locationname AS ln ON e.locationcode = ln.LocationId AND e.emploc = ln.LocId
    WHERE e.Id IS NOT NULL
) AS emp ON loc.Id = emp.Id
WHEN NOT MATCHED THEN
    INSERT (Id, No, Name)
    VALUES (emp.Id, emp.No, emp.Name)
    WHERE NOT EXISTS (
        SELECT 1
        FROM Location
        WHERE Id = emp.Id
    );
```
In this modified query, the WHERE NOT EXISTS condition is added to the INSERT statement. This condition checks if a row with the same Id already exists in the Location table. If it does, the row will not be inserted.

By adding this condition, the query will only insert rows into the Location table if they don't already exist, based on the Id column.

The expected output in the Location table, as provided in the question, will be achieved with this modified query.

Amit Mohanty2y ago

Try this:

MERGE INTO Location AS loc
USING (
    SELECT Id, MAX(No) AS No, MAX(Name) AS Name
    FROM Employee
    WHERE Id IS NOT NULL
    GROUP BY Id
) AS emp ON loc.Id = emp.Id
WHEN NOT MATCHED THEN
    INSERT (Id, No, Name) VALUES (emp.Id, emp.No, emp.Name);

See this db<>fiddle : https://dbfiddle.uk/wVajzeVK