Duplicate record check

• s.khann786

  Old Hand

  Points: 347

  More actions

  July 26, 2012 at 3:33 am

  #259381

  Hi Friends,

  i have record in xml like

  <root>

  <row spareid='10' sparename='abcd' />

  <row spareid='11' sparename='mno' />

  <row spareid='12' sparename='xyz' />

  </root>

  i want to check the spareid in table to avoid duplication before insert for all the records. if i get true from my store procedure then i can insert all record if any spareid is found in the table it does not add. give message something like flase. Please help me

  thanks

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  July 26, 2012 at 4:45 am

  #1517826

  s.khann786 (7/26/2012)

  ---

  Hi Friends,

  i have record in xml like

  <root>

  <row spareid='10' sparename='abcd' />

  <row spareid='11' sparename='mno' />

  <row spareid='12' sparename='xyz' />

  </root>

  i want to check the spareid in table to avoid duplication before insert for all the records. if i get true from my store procedure then i can insert all record if any spareid is found in the table it does not add. give message something like flase. Please help me

  thanks

  Hi,

  Hope this will be a good starting point

  DECLARE @XML XML

  SET @XML = '<root>

  <row spareid="10" sparename="abcd" />

  <row spareid="11" sparename="mno" />

  <row spareid="177" sparename="xyz" />

  <row spareid="11" sparename="hui" />

  </root>

  '

  IF EXISTS ( SELECT spareid ,

  COUNT(*) AS spareid_cnt

  FROM ( SELECT x.xml.value('(@spareid)[1]', 'int') AS spareid ,

  x.xml.value('@sparename[1]', 'varchar(25)') AS sparename

  FROM @xml.nodes('//root/row') x ( xml )

  ) AS tbl

  GROUP BY spareid

  HAVING COUNT(*) > 1 )

  BEGIN

  PRINT 'duplicates found'

  END

  ELSE

  BEGIN

  PRINT 'insert begins'

  -- do the insert

  END