fastest way to check if a table contains the data

[miro.cepciansky]

Hello!

I need to go through a database table and for each row
find out if some of its values are in another table in
another database. I could do this by:

DataAdapter1.Fill(ds1) //Data from the first database
foreach row in ds1
{
SQL2 = "SELECT * FROM TABLE2 WHERE col = ds1["col1"]
DataAdapter2.SelectCommand.Text = SQL2;
DataAdapter2.Fill(ds2);
if (ds.tables[0].rows.Count > 0)
...
}

The second database is quite large so this could take
some time. Is there a faster way to do this?

/Miro

 

[Soren Staun Jorgensen]

snip

DataAdapter1.Fill(ds1) //Data from the first database
foreach row in ds1
{
SQL2 = "SELECT * FROM TABLE2 WHERE col = ds1["col1"]
snip

You can use "SELECT COUNT(*) FROM TABLE2 WHERE col = ds1["col1"]"
which will return a single row with a single column containing the number of
records matching the filter

Soren

 

[Miha Markic]

You can use "SELECT COUNT(*) FROM TABLE2 WHERE col = ds1["col1"]"
which will return a single row with a single column containing the number of
records matching the filter

Or even faster, by doing a JOIN on the database.