merge statement insert issue

• Prasad.N

  Ten Centuries

  Points: 1083

  More actions

  March 18, 2013 at 11:56 pm

  #291000

  iam using merge statement in my proc.target table have lineid primary key.

  if i get not matched rescords from source table.below error message is displayed

  Msg 2627, Level 14, State 1, Line 7

  Violation of PRIMARY KEY constraint 'PK__t1__32489DA531F75A1E'. Cannot insert duplicate key in object 'dbo.t1'.

  The statement has been terminated.

  i given example code below

  create table t1(id varchar(10),ids int ,sal int,updatedDate datetime,lineId int primary key)

  insert into t1

  select 'r1',1,10,getdate(),1

  union all

  select 'r1',3,30,getdate(),2

  union all

  select 'r1',4,40,getdate(),3

  union all

  select 'r1',5,50,getdate(),4

  union all

  select 'r2',1,100,getdate(),5

  union all

  select 'r2',3,200,getdate(),6

  ----------------

  create table t2(id varchar(10),ids int ,sal int )

  insert into t2

  select 'r1',1,110

  union all

  select 'r1',3,30

  union all

  select 'r1',40,400

  union all

  select 'r1',55,550

  union all

  select 'r2',1,101

  ------

  declare @maxs int

  select @maxs=max(lineId) from t1

  ---------------------------------

  --DECLARE @T TABLE(id varchar(10),ids int);

  MERGE t1 AS T

  USING t2 AS S

  ON t.id = s.id and t.ids = s.ids

  --WHEN NOT MATCHED BY TARGET

  -- THEN INSERT VALUES (s.id, s.ids, s.sal,getdate()-1)

  WHEN MATCHED AND (t.sal != s.sal) THEN

  --Row exists and data is different

  UPDATE SET t.sal = s.sal, t.updatedDate=getdate()

  WHEN NOT MATCHED BY TARGET THEN

  INSERT(id,ids,sal,updateddate,lineid)VALUES (s.id, s.ids, s.sal,getdate()-1,@maxs+1);

  how to insert primary key columns.(inserts new records based on maximan lineid+1 )

  note : here i dont add identity constraint for that line id column.

  can you give me suitable solution

  Prasad.N
  Hyderabad-India.

• jerry-621596

  SSCertifiable

  Points: 6096

  More actions

  March 19, 2013 at 12:09 pm

  #1598588

  You are not using the primary key in the join between the SOURCE and TARGET. Your primary key also looks to be a more or less sequential value.

  Neither of these are going to work.

  Make the primary key in the tables be what the MERGE statement uses for the join and you will see more success.