insert missing rows

• huy1002

  SSC-Addicted

  Points: 448

  More actions

  January 21, 2013 at 1:57 pm

  #288197

  i am developing an web app allowing user to upload data from an .xls file to a "Temp" table. I want to insert only rows in Temp that are not in "MasterList" table

  Data in Temp

  ComputerName AuditID IAV Date

  AAA Au1 I1 1/1/13

  BBB Au2 I2 1/1/13

  CCC Au3 I3 1/1/13

  AAA Au4 I4 2/1/13

  Data in Master List

  AAA Au1 I1 1/1/13

  Please help me with a TSQL to insert BBB, CCC, DDD from Temp to MasteList only.

  Thank you.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 21, 2013 at 2:06 pm

  #1579380

  INSERT INTO MasterList(ComputerName, AuditID, IAV, [Date])

  SELECT ComputerName, AuditID, IAV, [Date]

  FROM Temp t

  WHERE NOT EXISTS (SELECT 1

  FROM MasterList ml

  WHERE ml.ComputerName = t.ComputerName

  AND ml.AuditID = t.AuditID

  AND ml.IAV = t.IAV

  AND ml.[Date] = t.[Date]);

  If I may make a suggestion, don't give your fields a name that's the same as a reserved word. Call it "EntryDate" or something like that instead of just "Date".

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions