SQL Server insert query avoiding duplicates

Question

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.

So how do you want to handle it? Don't insert? Insert a different Title_ID, if so how is that decided? — Aaron Dietz
– Aaron Dietz, Commented Oct 18, 2017 at 15:39
So what is that constraint? That should tell you what you need to avoid. — Keith
– Keith, Commented Oct 18, 2017 at 15:39
Possible duplicate of Violation of PRIMARY KEY constraint, Cannot insert duplicate key (MS SQL Server) — DrHouseofSQL
– DrHouseofSQL, Commented Oct 18, 2017 at 15:58

JohnHC · Accepted Answer · 2017-10-18 15:42:10Z

2

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)

answered Oct 18, 2017 at 15:42

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user726720 Over a year ago

it says invalid object name TableA in the last line

DrHouseofSQL · Accepted Answer · 2017-10-18 15:42:00Z

0

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/

answered Oct 18, 2017 at 15:42

DrHouseofSQL

5461 gold badge5 silver badges17 bronze badges

Comments

MatSnow · Accepted Answer · 2017-10-18 15:49:09Z

0

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)

edited Oct 18, 2017 at 15:49

answered Oct 18, 2017 at 15:42

MatSnow

7,5473 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Server insert query avoiding duplicates

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related