CODEWITHSUNDEEP
sqlsql-serversql-server-2005insert
Brezhnews
Brezhnews

Reputation: 1569

How to insert only if not exists

I am trying to insert data in table, but if it already exists in table, then it should not add.
This is the code, what I came up, but it still adds multiple data with same values.

insert into nol_art_izm([ART_ID],[DAT])
    select distinct
        v.id_art, {fn now()}
    from
        openxml(@hDoc, '/art_kompl/nol_voac') with #vc xd
        inner join nol_voac v on xd.id_art = v.id_art
    where
        not exists(select * from nol_art_izm where nol_art_izm.art_id=xd.id_art)


I want that there aren't any duplicate "ART_ID" values

Upvotes: 2

Views: 3246

Answers (2)

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

Try

insert into nol_art_izm([ART_ID],[DAT])
    select distinct
        v.id_art, {fn now()}
    from
        openxml(@hDoc, '/art_kompl/nol_voac') with #vc xd
        inner join nol_voac v on xd.id_art = v.id_art
        left join nol_art_izm n on n.art_id = v.id_art
    where n.art_id is null

Update:

Try using GROUP BY to avoid duplicate id_art values:

insert into nol_art_izm([ART_ID],[DAT])
    select
        v.id_art, MAX({fn now()})
    from
        openxml(@hDoc, '/art_kompl/nol_voac') with #vc xd
        inner join nol_voac v on xd.id_art = v.id_art
    where
        not exists(select * from nol_art_izm where nol_art_izm.art_id=xd.id_art)
    group by v.id_art

Please not that I have chosen the MAX function for aggregating the {fn now()} values (in case there are more such values per id_art). You might want to use another function.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

Note: This answer will only work with SQL Server 2008...

Use the MERGE statement. The advantage of a MERGE statement is the fact that it clearly expresses the intent of wanting to insert only if there is not yet a match. For future readers, this might be helpful, as alternatives involving INSERT .. SELECT are a bit more tricky to decipher.

-- This is where you're "merging" data into
MERGE INTO nol_art_izm dst

-- This is your merge data source
USING (
  -- Use DISTINCT here, to prevent possible duplicates from the below INNER JOIN
  SELECT DISTINCT v.id_art 
  FROM openxml(@hDoc, '/art_kompl/nol_voac') with #vc xd
  INNER JOIN nol_voac v on xd.id_art = v.id_art
) src

-- This is your "join" condition, used to decide whether to perform an
-- INSERT or UPDATE
ON (dst.art_id = src.id_art)

-- When source and target don't match (see ON clause), perform an insert
WHEN NOT MATCHED THEN INSERT ([ART_ID],[DAT])
  VALUES (src.id_art, {fn now()})

This statement omits the WHEN MATCHED THEN UPDATE clause, as you're only interested in performing INSERTs, not UPDATEs

Upvotes: 7

Related Questions