CODEWITHSUNDEEP
sqlsql-server
Xavier Dennis
Xavier Dennis

Reputation: 39

SQL Server Insert with out duplicate

I have a table with millions of records SQL DB. I want to insert a record if new one is not a duplicate record. But I dont want to check whether duplicate record exists. Is there any way to insert directly and if duplicate record exists just ignore the new insert?

Upvotes: 1

Views: 147

Answers (2)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

If you are inserting record from a Table

INSERT INTO INSERT_TABLE_NAME
(.....)
SELECT
(.....)
FROM TABLE_NAME T1
INNER JOIN INSERT_TABLE_NAME T2
ON T1.COLUMN_NAME1<>T2.COLUMN_NAME1
OR T1.COLUMN_NAME2<>T2.COLUMN_NAME2
OR ...

If you are inserting record by values

INSERT INTO INSERT_TABLE_NAME
(.....)
VALUES
(.....)
WHERE 
ON VALUE1<>T2.COLUMN_NAME1
OR VALUE2<>T2.COLUMN_NAME2

My solution is only suitable when Column in you table are in reasonable number.

Ofcouse @Damien_The_Unbeliever have given a better solution. But you can't implement it After some point.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You might be able to achieve what you want by applying a UNIQUE INDEX on the table and specifying IGNORE_DUP_KEY ON:

Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

ON

A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

So the insert will succeed for new unique rows, but you can't avoid the warning message.

You would create this index across all columns by which you're defining uniqueness/duplicates - which may or may not be all columns in the table - you haven't given us a definition to work from.

Upvotes: 1

Related Questions