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.

  • Select 1 record out of many for time period
  • How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query
  • SQL- Count occurrences of a specific word within all stored procedures
  • How to connect to a SQL Server database already hosted on server with windows phone 8
  • SqlTransaction has completed
  • Can someone explain DBCC DROPCLEANBUFFERS?
  • 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?

  • Failed to convert parameter C# / SQL Server
  • SQL: How to to SUM two values from different tables
  • SQL Querying Using Subquery
  • How to combine results of two queries into a single dataset
  • Dynamic Order by SELECT with multiple columns
  • Dividing two SQL query results
  • 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.