locked
Do not Insert Matching Records RRS feed

  • Question

  • I have a table with 9 fields in it.

    I am loading data from another table into this table.

    I want to update the existing data which matches all the 9 fields and insert those records which does not match all the 9 fields.

    What is the way of doing this with SQL? Please help

     

    Wednesday, April 20, 2011 5:11 AM

All replies

  • Hello,

    When all 9 fields matches, what do you want to update then; the values are already equal?

    To insert missing row you can use such a script; do a left join and filter on where destination is null = not existing:

    INSERT INTO DestinationTable

      (Field1, Field2, ..., Field9)

    SELECT SRC.Field1, ...,Src.Field9

    FROM SourceTable AS SRC

         LEFT JOIN DestinationTable AS DST

            ON SRC.Field1 = DST.Field1

               AND ...

               AND SRC.Field9 = DST.Field9

    WHERE DST.Field1 IS NULL;

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, April 20, 2011 5:27 AM
  • If you're using SQL Server 2008 or above, you might want to check out the MERGE statement - http://technet.microsoft.com/en-us/library/bb510625.aspx  MERGE effectively allows you to combine UDPATE and INSERT actions in a single statement depending on whether specified fields are matched between the source table and the target table.

    However, as Olaf has pointed out, your question doesn't quite seem clear - if both your source table and your target table only have 9 fields, how can you update the records that match all 9 fields? The records in this case will be identical so there's nothing to update...

    Also, does this have anything to do with SQL Server spatial? (Because you've posted this question in the SQL Server spatial forum, which is generally concerned with using the geometry and geography datatypes in SQL Server 2008+)


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, April 20, 2011 7:31 AM
    Answerer
  • The update is just on the time stamp to show that this record matched the incoming one.

    Let me try to insert logic.

    I am using SQL 2005.

    Wednesday, April 20, 2011 11:45 AM
  • My source is data dump from a flat file source. If selected 9 fields matches the records in the target table then just update the timestamp and do no insert. If any of the 9 fields does not match then insert the record. Other than the 9 fields there is last user and last user date in the table.

    My bad and apology for not being clear. I am using SQL Server 2005

    Wednesday, April 20, 2011 12:08 PM
  • Ok, to update the LastUserDate field you could use the statement below or you use the MERGE command as Tanoshimi suggested:

    UPDATE DST

    SET LastUserUpdate = GetDate()

    FROM  DestinationTable AS DST

          INNER JOIN

          SourceTable AS SRC

            ON SRC.Field1 = DST.Field1

               AND ...

               AND SRC.Field9 = DST.Field9

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, April 20, 2011 12:28 PM
  • Olaf,

    This logic is inserting records. But it is not eliminating the existing one. It still inserts one for the new record

    Wednesday, April 20, 2011 3:09 PM
  • No, it compares source and destination datarows and updates in destination the timestamp for all equal datasets.
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, April 20, 2011 3:41 PM
  • Update is working fine. It find the existing record and updates it. But the insert is killing me.

    LEFT JOIN DestinationTable AS DST

            ON SRC.Field1 = DST.Field1

               AND ...

               AND SRC.Field9 = DST.Field9

    WHERE DST.Field1 IS NULL;

     

    ON SRC.Field1 = DST.Field1 when this is true, this field is not null (DST.Field1 IS NULL) That is where it is failing I think.

    Wednesday, April 20, 2011 4:02 PM
  • No help???

    All I want is to eliminate the already existing record from the set I am trying to insert.

    My query is like this

    insert into master_detail

    select F1,F2,getdate(),getdate(), 'user master', f3,f4,f5

    from loading table

    inner join master table on load_id=load_id

    I already have records in master_detail with value F1,F2,F3,F4,F5. I just want to eliminate them

    please help!!!!

     

     

     

    Wednesday, April 20, 2011 8:54 PM