Avoid duplicates in INSERT INTO SELECT query in SQL Server

Merge like a pro

Merging tables in SQL is akin to combining superhero teams. With MERGE, you can make sure the Avengers don't end up with two Iron Mans:

-- When two become one, without the duplicates!
MERGE target AS t
USING source AS s
ON t.id = s.id
WHEN NOT MATCHED BY TARGET THEN
    INSERT (cols) VALUES (s.cols);


With UNIQUE INDEX, your database transforms into an exclusive club, stopping any attempt to insert violating data:

-- The VIP room where duplicates can't enter!
CREATE UNIQUE INDEX idx_target_id ON target(id);


Feeling lazy? Do the work upfront! Make DISTINCT take care of the duplicates in your SELECT query.

Equip the right armor

The duelist: Merge vs distinct

To choose the best method for your use case, consider the size of both your tables and frequency of your DML operations. While the MERGE statement provides flexibility, DISTINCT and NOT EXISTS can be more efficient for simpler operations.

The Vanguard: Indexes and constraints

Indexes and constraints are your defense against violations of PRIMARY KEYS and UNIQUE CONSTRAINTS. An index optimizes query performance, but remember: with great power comes great responsibility. Over-indexing can be your downfall!

The Troubleshooter: Error handling

Be prepared to handle any curveballs that SQL Server might throw at you. Use TRY-CATCH blocks to keep your queries running smoothly:

-- When life gives you errors, make lemon...oops, I mean, run exception handling! 
BEGIN TRY
    -- Go for the insert!
END TRY
BEGIN CATCH
    -- Oops! There was a hiccup
END CATCH


The Strategist: Testing and optimization

Analyze your tactics! Consider using query performance tuning tools like SQL Profiler or the Query Store to benchmark your insert methods and ensure you're employing the most efficient strategy.