1

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?

Xavier Dennis
  • 39
  • 1
  • 1
  • 7
  • 1
    check MERGE statement: http://technet.microsoft.com/en-us/library/bb510625.aspx – Vishal Gajjar Jun 07 '13 at 11:08
  • possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – Lukas Eder Jun 07 '13 at 11:12
  • And also others: http://stackoverflow.com/questions/9638185/insert-into-ignore-duplicates-that-are-not-the-primary-key, http://stackoverflow.com/questions/8854997/insert-ignore-and-on-duplicate-key-update-not-working-in-sql-server-2008-r2, etc. – Lukas Eder Jun 07 '13 at 11:13
  • Either use MERGE statement or INSERT... SELECT ... WHERE NOT EXISTS. – Serge Jun 07 '13 at 11:32
  • How will you know if the record is duplicate without checking? All solutions posted by others in comments, will check if the record exists. – Raj Jun 07 '13 at 12:02
  • LEFT Join with the table? – Nilesh Thakkar Jun 07 '13 at 12:29

2 Answers2

1

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.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

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.

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71