CODEWITHSUNDEEP
sqlsql-servert-sql
user726720
user726720

Reputation: 1237

SQL Server insert query avoiding duplicates

I need to insert into TableA from TableB and avoiding duplicates, I tried the below it gives an error:

Violation of Primary Key constraint 'PK_TableA'. Cannot insert duplicate key ...

 insert  into TableA (DataField, TitleId, ClassId, ValueOrder, StrValue)
    select 'Somtext',[title_id],'-1', '1', 
          [reference_text]
    from TableB
    where TableB.SomeColumn ='sometext 2014'



TableA
Title_id  reference_text
1234       ABCD   
1487       XFRE
1434       DERE

TableB
DataField  TitleId  ClassId  ValueOrder  StrValue

Now the Problem here is Data already exists in TableB. I need to avoid when TableA.Tile_id = TableB.TitleId, when this matches.

Upvotes: 0

Views: 78

Answers (3)

MatSnow
MatSnow

Reputation: 7517

Adding a NOT IN to the WHERE-clause like this should work:

WHERE TableB.SomeColumn ='sometext 2014'
AND TableB.TitleId NOT IN (SELECT Tile_Id FROM TableA)

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Use a where not exists

insert  into TableA (DataField, TitleId, ClassId, ValueOrder, StrValue)
select 'Somtext',[title_id],'-1', '1', 
       [reference_text]
from TableB
where TableB.SomeColumn ='sometext 2014'
and not exists (select 1 from TableA ta where ta.TitleID = TableB.title_id)

Upvotes: 2

DrHouseofSQL
DrHouseofSQL

Reputation: 548

Take a look at this article, you could do a merge and when matched then (delete/update/insert).

https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

Upvotes: 0

Related Questions