CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Aesir
Aesir

Reputation: 2483

Prevent the insertion of duplicate rows using SQL Server 2008

I am trying to insert some data from one table into another but I would like to prevent the insertion of duplicate rows. I have currently the following query:

INSERT INTO Table1
(
    Table1Col1,
    Table1Col2,
    Table1Col3,
    Table1Col4,
    Table1Col5
)
SELECT
    Table2Col1,
    Table2Col2 = constant1,
    Table2Col3 = constant2,
    Table2Col4 = constant3,
    Table2Col5 = constant4
FROM Table2
WHERE
    Condition1 = constant5
AND
    Condition2 = constant6
AND
    Condition3 = constant7
AND
    Condition4 LIKE '%constant8%'

What I do not know is that the row I am trying to insert from Table2 into Table1 might already exist and I would like to prevent this possible duplication from happening and skip the insertion and just move onto inserting the next unique row.

I have seen that I can use a WHERE NOT EXISTS clause and use of the INTERSECT keyword but I did not fully understand how to apply it to my particular query as I only want to use some of the selected data from Table2 and then some constant values to insert into Table1.

EDIT:

I should add that the columns TableCol2 through to TableCol5 don't actually exist in the result set and I am just populating these columns alongside Table2Col1 that is returned.

Upvotes: 2

Views: 2955

Answers (3)

Abdul Kareem
Abdul Kareem

Reputation: 64

we have check the whether the data is already exist or not in table. For this we have to use If condition to avoid the duplicate insertion

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Intersect (minus in Sql Server's terms) is out of question because it compares whole row. Other two options are not in/not exists/left join and merge. Not In is for single-column prinary key only, so it is out of question in this instance. In/Exists/Left join should have the same performance in Sql Server, so I'll just use exists:

INSERT INTO Table1
(
    Table1Col1,
    Table1Col2,
    Table1Col3,
    Table1Col4,
    Table1Col5
)
SELECT
    Table2Col1,
    Table2Col2 = constant1,
    Table2Col3 = constant2,
    Table2Col4 = constant3,
    Table2Col5 = constant4
FROM Table2
WHERE
    Condition1 = constant5
AND
    Condition2 = constant6
AND
    Condition3 = constant7
AND
    Condition4 LIKE '%constant8%'
AND NOT EXISTS
    (
       SELECT *
         FROM Table1 target
        WHERE target.Table1Col1 = Table2.Table2Col1
          AND target.Table1Col2 = Table2.Table2Col2
          AND target.Table1Col3 = Table2.Table2Col3
    )

Merge is used to sync two tables; it has ability to insert, update and delete records from target table.

merge into table1 as target
using table2 as source
   on target.Table1Col1 = source.Table2Col1
  AND target.Table1Col2 = source.Table2Col2
  AND target.Table1Col3 = source.Table2Col3
when not matched by target then
  insert (Table1Col1,
          Table1Col2,
          Table1Col3,
          Table1Col4,
          Table1Col5)
  values (Table2Col1,
          Table2Col2,
          Table2Col3,
          Table2Col4,
          Table2Col5);

If columns from table2 are computed during transfer, in not exists() case you might use derived table in place of table2, and the same applies to merge example - just place your query in place of reference to table2.

Upvotes: 1

Diego
Diego

Reputation: 36176

Since you are on SQL Server 2008, you can use a merge statement.

You can easily check if a row exists base on a key

something like this:

merge TableMain  AS target
using TableA as source 
ON <join tables here>
WHEN MATCHED THEN <update>
WHEN NOT MATCHED BY TARGET <Insert>
WHEN NOT MATCHED BY SOURCE <delete>

Upvotes: 3

Related Questions