INSERT INTO EXCEPT using only 2 identify columns

hopefully this is an easy one for you folk smarter than me. I am trying to perform an insert into a table from another matching table, but I only want to insert if the row doesn’t already exist. Some of these values in the row if it exists may be changed. Which is okay.

I’m thinking if two of the columns match upon insert, then don’t insert and leave record as is. If those two columns don’t match then insert the row. I have looked through merge, and also INSERT INTO EXCEPT. This code will work: but it is not what I need.

  • Create DATE (first day of week) column using data from two existing columns
  • MSSQL JDBC: Join Query Performance _sometimes_ very slow
  • Java, SQL Database Connection
  • Deleting Exact Duplicates
  • Create nonclustered primary keys using NHibernate and SchemaExport
  • Could someone please provide a walkthrough for deploying a SQL Server / VC# app?
  • INSERT INTO LotDetail
    SELECT UID, LotKey, SerialNo, TypeID, HotWeight, YieldGradKey, PeltGrade, 
    WeightRange, MarketValue, PremiumDiscount, PeltValue, OffalValue, 
    PricingKey, USDAGrade, 1506488016 FROM LotDetail
    EXCEPT
    SELECT UID, LotKey, SerialNo, TypeID, HotWeight, YieldGradKey, PeltGrade, 
    WeightRange, MarketValue, PremiumDiscount, PeltValue, OffalValue, 
    PricingKey, USDAGrade, 1506488016 FROM LotDetail_STAGING
    

    The problem with the above code is that I must match all columns otherwise it tells me the table definition doesn’t match. I only want to timestamp and serialno as the identifiers. I don’t want to match on the other fields as they may change.

    The problem is that if the record exists on the LotDetail table already and the ‘hotweight’ or ‘marketvalue’ have changed, I want to preserve those changes and not overwrite the record. I am simply looking to insert ONLY when ‘serialno’ and ‘timestamp’ don’t exist. Those fields cannot be changed. Any thoughts?

  • How do I Sort a varchar with numbers and letters without a specific format?
  • SQL email rows not aligning
  • Calculate total working days left of the month
  • Replace spaces using function in sql
  • If else update sql query to combine multiple queries
  • Use computed column of stored procedure in same SP at other place
  • One Solution collect form web for “INSERT INTO EXCEPT using only 2 identify columns”

    Hmmm, I think you have your tables backwards. It makes more sense to me to insert from the “staging” table into the final table.

    Then, you can use NOT EXISTS instead of EXCEPT:

    INSERT INTO LotDetail(UID, LotKey, SerialNo, TypeID, HotWeight, YieldGradKey, PeltGrade, 
                          WeightRange, MarketValue, PremiumDiscount, PeltValue, OffalValue, 
                          PricingKey, USDAGrade, 1506488016
                         )
        SELECT UID, LotKey, SerialNo, TypeID, HotWeight, YieldGradKey, PeltGrade, 
               WeightRange, MarketValue, PremiumDiscount, PeltValue, OffalValue, 
               PricingKey, USDAGrade, 1506488016
        FROM LotDetail_STAGING ls
        WHERE NOT EXISTS (SELECT 1
                          FROM LotDetail l
                          WHERE l.SerialNo = ls.serialNo AND
                                l.1506488016 = ls.1506488016
                         );
    

    I also think it is a good idea to list the columns when doing an INSERT.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.