Comparing to recordsets, finding difference, saving all columns

Lance James 366 Reputation points
2022-03-22T13:06:19.323+00:00

I read a spreadsheet into a c# datatable and process it to be a match a SQL table where the data will be stored.

The data to store is only where the newly created datatable contains data that currently isn't in the SQL table.

I planned on doing a query between the datatable and the SQL table to find those datatable records to add.

Problem: I can't compare the entire row/record as each row will have columns that are unique to each record but aren't important in determining if a record should be included.

Example: I inject the date the record was added to the SQL table as a column.  Therefore, I cannot include this column in the comparison because it will be 
different than the datatable column (current date) which I am using in the comparison.

Ultimately, I must choose just the columns that need to be verified which provides the recordset to include but then include all the columns in that recordset and not just the
columns used for the comparison to generate the recordset.

Not being an SQL person in general, what would one recommend as the best strategy to develop the final complete recordset to add to the SQL table?

Regards,
Lance

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2022-03-22T17:35:09.1+00:00

    I repost it here:

    INSERT INTO DestinationTable
    SELECT s.*
    FROM DataSet AS s
    WHERE NOT EXISTS (
        SELECT 1 
        FROM DestinationTable
        WHERE UniqueCol = s.UniqueCol -- Add more if more columns make unique
    );
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2022-03-22T13:33:27.38+00:00

    If there is a column or a combination of multiple columns which makes an unique, you can use the SQL MERGE statement to archive your task.. First you need to import data from the spreadsheet to a staging table as a source table and then you can use the MERGE statement to insert data to the destination table when the unique columns do not match between the source table and the destination table. Something like this:

    MERGE DestinationTable AS t  
    USING (  
    	SELECT Col1, Col2, ...   
    	FROM SourceTable  
    ) AS s ON t.UniqueColumn = s.UniqueColumn  -- Add more if more columns make unique  
    WHEN NOT MATCHED THEN  
    	INSERT (Col1, Col2, ...)  
    	VALUES (s.Col1, s.Col2, ...);  
      
    

  2. Lance James 366 Reputation points
    2022-03-22T16:29:24.19+00:00

    Solution provided by GuoxiongYuan-7218 as a comment above. Added here to Accept the answer.

    >  GuoxiongYuan-7218  LanceJames-3930 · 14 minutes ago
    
    Maybe you can try this:
    
     INSERT INTO DestinationTable
     SELECT s.*
     FROM DataSet AS s
     WHERE NOT EXISTS (
         SELECT 1 
         FROM DestinationTable
         WHERE UniqueCol = s.UniqueCol -- Add more if more columns make unique
     );
    

    Someone please tag this as the Accepted Solution. I cannot since I am posting it but on behalf of the author.

    Regards,
    Lance