Using arrays in SQL Select statement

I have two databases that I'm pulling data from via ODBC connections in VB.NET. I was thinking of getting an array of indices from the result of a query to one database, and then using that array in a query to get the results I want in the other database. Is it possible to use an array in a WHERE clause?

Also, would there be a easier/better solution to getting data in common from two separate databases via ODBC connection?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
No, you can't use arrays. They are no valid SQL object. Usually you create a dynamic IN list for the values of that array.

A more advanced option is (with MSSQL on one side) to use a linked server. That can be a ODBC connection to many other DBMS brands or direct connection to another MSSQL. The latter one would use the 4-dot notation:

select *
from server1.db1.dbo.table1 t1
join  table2 t2
on =
If both DBs are on the same server (and instance), you can always use the 3-dot notation
db.schema.object, to refer to one of the tables. That would then look like this:

select  *
from db1..table1 t1
join db2..table2 t2
on =
(you can omit dbo as schema owner, hence i used .. above).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
diablo089Author Commented:
How do use a linked server?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to create a linked server first. That is best done in Management Studio, in Server Objects, Linked Servers. For a MSSQL target, the linked server name has to be the same as in a connection string, that is either  machine  or  machine\instance. For other DBMS, you can choose arbitrary - however, what you have to provide exactly on the other fields is depending on the driver you choose.

After setting up that page, you need to go to the Security page in the same dialog box, to set up authentication. Usually you will either use "Be made using the login's current security context" (the same credentials as in this MSSQL server will be used), or "Be made using this security context" (with allows for providing a single explicit login).

If everything is set up correctly, you can use the server.db.schema.object syntax for MSSQL targets, and OpenQuery(server, 'select ....') for non-MSSQL. Example for the latter:
   select * from openquery(oracleserver, 'select count(*) from dual');

Another option is to build a filter expression by looping in the first table(if the number of records is not very high). Here is example code which I use in my app

Dim Filter As String

If cbLive.Checked Then
   Filter = "'Live',"
End If
If cbLost.Checked Then
   Filter &= "'Dead',"
End If
If cbWon.Checked Then
   Filter &= "'Won'"
End If

Filter = Filter.Trim().Trim(",").Trim()

If Not String.IsNullOrEmpty(Filter) Then
sqldataadapter.SelectCommand.CommandText = "Select ... Where Status IN (" & Filter & ")"
End If

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.