Preventing Duplicate Table Inserts

The database is SQL Server 2008. I have a query which extracts rows from one or more tables and then attempts to insert them into a table variable.

I'd like an efficent way to prevent duplicate inserts, so what I came up with was:

INSERT INTO @MyTableVariable SELECT SomeID FROM SomeTable st INNER JOIN SomeOtherTable sot ON sot.SomeID = st.SomeID LEFT JOIN @MyTableVariable t ON t.SomeID = sot.SomeID WHERE t.SomeID IS NULL

This, however, does not appear to prevent duplicate inserts in certain scenarios.

It seems as if (and this makes sense if you think about it and look at the query plan) that only the initial "state" of @MyTableVariable is used in the left join operation. In other words, this would prevent duplicates if @MyTableVariable already had SomeID in it before this statement was run, but would not prevent duplicates if the FROM/INNER JOIN on SomeTable/SomeOtherTalbe resulted in duplicate SomeIDs.

Aside from simply slapping a DISTINCT on the SELECT statement, is there another more efficent way to handle this?


As far as I know, there's no way of INSERT IGNORE or INSERT ON DUPLICATE KEY in SQL Server. There is MERGE, of course, but it wouldn't solve your problem, because it would behave same as your INSERT, i.e. it would raise an exception.

is there another more efficent way to handle this?

In my view, your options are:

  1. Attempt to find a more specific way of filtering/joining so as not to produce duplicates.
  2. 'Slap' DISTINCT somewhere at an earlier stage so as to prevent duplicates from entering either of the tables being joined in the first place.
  3. Transform the table(s) primarily responsible for producing duplicates, into subselects with DISTINCTs applied locally.

If you cannot produce a duplicate-free result set, you'll have to pay (performance-wise) for eliminating possible duplicates. Whatever it could be, DISTINCT, or GROUP BY, or maybe ranking functions, it would induce some performance penalty, and you should just accept the fact.

You need to create key on the id column of the table variable.

declare it like this:

declare @MyTableVariable table(SomeID int identity(1,1) primary key)

This primary key will prevent duplicate insert

Hope this helps

Category:sql server 2008 Time:2011-06-17 Views:2

Related post

  • preventing duplicate table inserts using jdo in android connected appengine 2012-03-18

    I would like to know how to prevent duplicate inserts when doing an RPC call from an Android client connected to app engine. Below is my code and what I tried at the back-end but when I do this I get an "Internal Server Error". public void createenti

  • Prevent duplicate record insert on manual page refresh 2010-09-16

    I have a problem which has only just surfaced itself. I'm working in a MVC environment. The method names in my interface class match those of the request module and action i.e. ?module=test&action=action would result in a method called public fun

  • Prevent duplicate record insertion on refresh without redirecting 2011-01-30

    I have this bit of script: if (isset($_POST['comment_posted'])) { $user_comment = mysql_real_escape_string($_POST['user_comment']); $add_user_comment = Event::addUserComment($id,$user->user_id,$user_comment); } After a user submits his comment, an

  • preventing duplicates when inserting nodes to treeview control 2011-06-09

    I want to create a hierarchical view of strings based on first two characters. If the strings are: AAAA,AAAA,BBDD,AABB,AACC,BBDD,BBEE I want to reate a treeview that looks like this: AA AAAA AABB AACC BB BBDD BBEE I currently have some code that look

  • Prevent duplicate database table entries 2010-08-30

    To prevent duplicate table entries in a database I use a primary key. I just add the information and if it is a duplicate then the primary will be a duplicate and it will not add to the table. Should I also do a SQL query (before trying to add to the

  • Prevent duplicate insert without session 2011-01-31

    im working on sharepoint 2010 (using visual webpart) is there any way to prevent duplicate insert in a list when page is refreshed using F5, without using session thx --------------Solutions------------- The sharepoint "way" is usually to redirect to

  • Preventing Duplicate Inserts Into SQL With PHP 2008-11-09

    I'm going to running thousands of queries into SQL and I need to prevent the duplication of field 'domain'. Never had to do this before and any help would be appreciated. --------------Solutions------------- You probably want to create a "UNIQUE" con

  • How to prevent duplicate rows in insert query using Oracle? 2009-10-31

    I have table A in Oracle that has a primary key (id). I need to insert data into this table. How do I prevent duplicate rows? --------------Solutions------------- If the id column is marked as the PK you will not be able to insert a duplicate key, th

  • How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key 2010-04-07

    I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique reco

  • How to prevent duplicate values from inserting mySQL considering two columns? 2011-10-20

    Here is my table: Table Name: UserLinks Link_ID User_1 User_2 1 234325 100982 2 116727 299011 3 399082 197983 4 664323 272351 Basically, in this table a duplicate value is: Link_ID User_1 User_2 1 232 109 2 109 232 I have looked around and found that

  • How best to upload data to a database table and prevent duplicate entries? 2012-02-03

    I am trying to prevent duplicate copies in a table. I am looking for duplicates where the entire row is a copy of another row. If one element of a row is a copy of somehting from another row, that is not a problem. For example, if the name is repeate

  • How to use SqlBulkCopy to prevent duplicates insert? 2012-03-29

    I am going to use SqlBulkCopy, but I need to prevent duplicates insert. What is the best way to do it? Have I do check existence for each item before make execute WriteToServer command? --------------Solutions------------- Bring all the values form t

  • Insert Query (that prevents duplicates) 2014-09-07

    I saw a question similar to mine and I tried to modify the code but to no avail. I have a multiple-field index but it doesn't seem to be stopping duplicates from being imported or entered. I will post here the fields in the index as well as the code

  • MySQL ON DUPLICATE KEY insert into an audit or log table 2010-10-07

    Is there a way to accomplish this? INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3) ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored') I really don't want to use PHP for this :( Thanks! --------------Solutions-

  • Rails: Prevent duplicate inserts due to pressing back button and save again 2011-01-11

    Think about a simple Rails scaffold application with a "new" action containing a form to add records to a database with a "save" button. After the "create" action the controller redirects to the "show" action, where the user can use the "edit" link t

  • How can I check for duplicates before inserting into a table when inserting by select 2011-04-08

    How can I check for duplicates before inserting into a table when inserting by select: insert into table1 select col1, col2 from table2 I need to check if table1 already has a row with table1.col1.value = table2.col1.value, and if yes, then exclude t

  • Prevent Duplicate Records in Many to Many Join Table 2015-01-06

    I have a Many to Many relationship between a Member table and an Event table (One member can go to many events and one event can hold many members). I use the recommended join table to properly create the relationship using the PKs from the Member an

  • Prevent Duplicate Entries and alert via a alert box if user insert duplicate entry 2010-07-09

    How i Prevent Duplicate Entries in database at the time of form submit and display an alert box and stop form submission if entry is duplicate. --------------Solutions------------- You can add a validates_uniqueness_of validation to your model: class

  • How can I constrain multiple columns to prevent duplicates, but ignore null values? 2009-03-23

    Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected. create table sandbox(a number(10,0), b number(10,0)); create uniq

Copyright (C), All Rights Reserved.

processed in 0.245 (s). 13 q(s)