Copies row into the same table if not exists

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  February 1, 2012 at 5:47 pm

  #263893

  I have a query like below, I would like to

  INSERT INTO orgAllocationJobCode (

  JobCode,

  AllocationCostCenter,

  AllocationType,

  VersionCode,

  SchoolYear,

  CreateDate,

  CreatedBy,

  ChangeDate,

  ChangedBy

  )

  SELECT JobCode,

  AllocationCostCenter,

  AllocationType,

  @WorkingVersion, SchoolYear,

  CreateDate,

  CreatedBy,

  ChangeDate,

  ChangedBy

  FROM orgAllocationJobCode

  WHERE VersionCode = @OriginalVersion AND SchoolYear = 2012

  This query is basically to copy the original version of records into work copy to the same table.

  Because sometimes we want to do incremental add, so I would like to add for the query to only inserts those are not exist in the same table.

  how could I change the query to do this?

  Thanks

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 1, 2012 at 5:54 pm

  #1441501

  You could use the MERGE statement. For an explanation and an example go to

  http://technet.microsoft.com/en-us/library/bb510625.aspx

  or

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

  or

  SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

  http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 2, 2012 at 7:13 am

  #1441805

  Ignore ... misread OPs post

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• johnitech.itech

  Hall of Fame

  Points: 3252

  More actions

  February 6, 2012 at 7:14 am

  #1443107

  INSERT INTO destTable

  SELECT Field1,Field2,Field3,...

  FROM srcTable

  WHERE NOT EXISTS(SELECT *

  FROM destTable

  WHERE (srcTable.Field1=destTable.Field1 and

  SrcTable.Field2=DestTable.Field2...etc.)

  )

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  February 6, 2012 at 11:03 am

  #1443340

  Thanks all, I will give it a try