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?
diablo089Asked:
Who is Participating?
 
QlemoConnect With a Mentor 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 t1.id = t2.id
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 t1.id = t2.id
(you can omit dbo as schema owner, hence i used .. above).

0
 
diablo089Author Commented:
How do use a linked server?
0
 
QlemoBatchelor, 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');

0
 
CodeCruiserCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.