SQL query for inserting rows into table if not exist

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

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.

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