How to select records from SQL DB that contain the same ID as a .NET datatable records

Question

I'm loading a CSV file containing many thousands of rows into a .NET DataTable that looks something like this:

MyLocalDataTable SequenceNo, Etc

I have a table in an SQL database and I'd like to pull records into another datatable based on what I find in the CSV file.

MySQLBDTable IdentityID, SequenceNo, Etc

The SequenceNo is my common field. I'd like to do a SELECT on MySQLBDTable that only returns the rows with a SequenceNo from MyLocalDataTable. I'm not sure how to do this. I want to avoid a SELECT query with thousands of WHERE SequenceNo= clauses.

Is there a simple and efficient way to do this?

Answer 1

Dump the sequence number values into a staging table, a "temporary" table that contains only one column, of the right type to hold a sequence number.

If you're using SQL Server, you can do this using bulk insert, which is very fast.

Then, you execute a SQL statement that uses the contents of that staging table for a join or a IN-clause to find the existing rows in the database.

For instance:

UPDATE yourtable
SET does_exist = 1
WHERE sequence_no IN (SELECT sequence_no FROM staging_table)

Answer 2

Going along with what Lasse said, you can use SQLBulkCopy to do a bulk insert from your local data table directly into SQL Server.

Answer 3

You can generate query like this:

var numbers = localTable.Rows.Cast<DataRow>()
    .Select(r => r.Field<string>("SequenceNo"))
    .ToArray();

var queryTemplate = @"select * from MySQLBDTable where IdentityID in ({0})";

var queryText = string.Format(queryTemplate, numbers);

Please note that if you have too many rows in your local table, you may hit the limit of items in IN - in this case change your query into something like this:

select * from MySQLBDTable where IdentityID in (1,2,3,4,5,6,7,8,9,10)
union
select * from MySQLBDTable where IdentityID in (11,12,13,14,15,16,17,18,19,20)