Only inserting a row if it's not already there

sql

database-management

data-integrity

concurrency-control

byAlex Kataev·Nov 15, 2024

To prevent duplicate entries, use the INSERT INTO ... SELECT approach along with a WHERE NOT EXISTS clause. This ensures only distinct rows are added to the table.

INSERT INTO table_name (column1, column2)
SELECT 'value1', 'value2' WHERE NOT EXISTS (
    SELECT 1 FROM table_name WHERE column1 = 'value1' AND column2 = 'value2'
);

Just replace table_name, column1, column2, value1, value2 to fit your table and data.

Safe journey: ensuring row uniqueness

Unique indexes or constraints form a solid guard against duplicate entries. Creating a unique index is like hiring a top-notch bodyguard for your data: impeccable screening and highly reliable.

CREATE UNIQUE INDEX idx_unique_columns ON table_name (column1, column2);

With this setup, attempts to insert duplicate rows will result in an error, which you can gracefully handle using TRY/CATCH blocks:

-- Our bouncer ready to catch any duplicate troublemakers!
BEGIN TRY
  INSERT INTO table_name (column1, column2)
  VALUES ('value1', 'value2');
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    PRINT 'Oops, a duplicate tried to sneak in!'
  ELSE
    PRINT 'Some other trouble is brewing.'
END CATCH

All roads lead to Rome: when to update instead of inserting

Sometimes, curtailing duplicate entries requires not a rejection, but an update. That's where MERGE drops in — your data's welcoming but discerning host, allowing for a neat blend of both insert and update operations:

MERGE INTO table_name USING 
(VALUES ('value1', 'value2')) AS src (column1, column2)
ON table_name.column1 = src.column1 AND table_name.column2 = src.column2
WHEN MATCHED THEN 
  UPDATE SET column1 = src.column1, column2 = src.column2
WHEN NOT MATCHED THEN 
  INSERT (column1, column2) VALUES (src.column1, src.column2);

The MERGE statement is the perfect diplomat, making peace between new and existing data.

Quiet watchman: exception handling and transaction management

TRY/CATCH blocks and TRANSACTIONS form the backbone of resolute, fuss-free SQL operations. Think of them as the night watchmen of your data, always alert, always ready to catch any sneaky errors.

BEGIN TRANSACTION
BEGIN TRY
  -- Your powerful insert or merge action here
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627 
    RAISERROR('Hey, a clone sneaked in!', 16, 1);
  ROLLBACK TRANSACTION;
END CATCH
COMMIT TRANSACTION

All hands on deck: concurrency and lock handling

To deal with concurrent operations, consider HOLDLOCK or UPDLOCK. It’s a careful balancing act of watching the crowd without slowing down the heart of the party.

-- Let our SQL DJ mix the tracks without a hitch!
BEGIN TRANSACTION
SELECT * FROM table_name WITH (UPDLOCK, HOLDLOCK)
WHERE column1 = 'value1' AND column2 = 'value2';
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
COMMIT TRANSACTION

Balancing consistency and concurrency can feel like running a successful club night — exhilarating when you get it right!

No crowd surfing: tackling edge cases and potential problems

In the sea of data, race conditions can surf on unnoticed, wreaking havoc later. Common pitfalls — like the IF (SELECT COUNT(*)..) approach — are deceptively inviting beachheads for such problems. It’s wiser to stick to proven, reliable methods like NOT EXISTS and MERGE.

-- Avoid having too MANY crowd surfers
IF NOT EXISTS (SELECT * FROM table_name WHERE column1 = 'value1')
  INSERT INTO table_name (...)
ELSE
  -- Sigh! The crowd surfer got noticed. Let’s just go with it.
  PRINT 'Oops.. someone duplicated!'