SQL Insertion without duplication

Question

Is there a specific command for SQL Server in order to INSERT a lot of rows with the condition : if a row already exists in database doesn't duplicate it during insertion ?

Edited

In a sqlbulkcopy, I'd like to avoid exception because a row is already in the table ?

Answer 1

You can use the MERGE command for this. Example Usage.

CREATE TABLE #A(
 [id] [int] NOT NULL PRIMARY KEY CLUSTERED,
 [C] [varchar](200) NOT NULL)


    MERGE #A AS target
    USING (SELECT 3, 'C') AS source (id, C)
    ON (target.id = source.id)
    /*Uncomment for Upsert Semantics
       WHEN MATCHED THEN 
        UPDATE SET C = source.C */
    WHEN NOT MATCHED THEN    
        INSERT (id, C)
        VALUES (source.id, source.C);

Edit Though you say in your edit this is for a bulk copy? You could also investigate the "Ignore Duplicate Keys" option on your index.

Answer 2

How to do it in T-SQL is discussed here (article is a bit old though)