0

I have two data tables which have a primary key column in common but otherwise different structures as they're created from different tables. I'd like to check which rows are missing from data table B compared to A, with the aim of adding in the missing rows to the database.

I've had a look at questions like this one, which provide good answers but the data tables are usually assumed to be the same.

Since I want to get a list of new rows for inserting, is there any way I can do this without iterating through the rows and checking the PKs? If I'm not mistaken, Merge will keep the old rows, which I don't want to resubmit to the database.

Both tables will have only a few dozen rows, a couple of hundred maximum. Would the computational hit of doing it row-by-row be negligible anyway?

edit: the two source tables are on different databases/servers, so I can't easily do the comparison in SQL. I'd prefer the consuming application to do it anyway, as I may add some more row processing before I'm done.

Community
  • 1
  • 1
Alex
  • 2,681
  • 3
  • 28
  • 43
  • I could only answer with, and suggest iteration. I can't think of a function that will add only n rows to a data table from another data table when a record has a column value that might not exist in the the first data table. – Andrew Jackman Jul 18 '11 at 10:05

2 Answers2

1

I hope I've understood your needs correctly, but how about using Linq? Load the keys of your 2 tables into 2 IEnumerable<keytype> objects, e.g. (assuming your keys are of type int)

IEnumerable<int> keys1, keys2;
// code to load the keys into these collections
// to find the keys in keys1 that are missing from keys2:
var missingKeys = keys1.Where(k1 => !keys2.Any(k2 => k2 == k1));

HTH

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • Thanks - I really should have thought of this myself, I got hung up on the fact that the majority of the table structures are different! :) – Alex Jul 19 '11 at 08:33
0

Is it not easier/quicker to do this in SQL e.g:

SELECT col1 FROM table1
MINUS
SELECT col1 FROM table2
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
  • yes, but the two tables are actually on different databases, and probably on different servers :) – Alex Jul 18 '11 at 10:31