Comparing to recordsets, finding difference, saving all columns

Question

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

Answer 1

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
);

Answer 2

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, ...);  
  

Answer 3

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