CODEWITHSUNDEEP
sqldatabaseasp-classicvbscript
Rene Zammit
Rene Zammit

Reputation: 1151

SQL get data from different databases

I have 2 databases on the server names db_mdone and db_mdtwo. One of the columns in EACH database is the same named "Email" . That said, both databases have different columns. Can someone please help me in the SQL query to match the columns together and get the records? The records of db_mdone database is going to show at the top of the page and the records of the other is going to show at the bottom of the page. They dont need to be joined. thanks

Upvotes: 0

Views: 11988

Answers (4)

Diego
Diego

Reputation: 36146

you didnt mentioned table names. You said that one of the columns in each DATABASE is called email. I imagine you have a table in the middle :)

Nevertheless, all the solutions provided so far don't deal with your last statement: The records of db_mdone database is going to show at the top of the page and the records of the other is going to show at the bottom of the page

Again, I imagine that by that you want all the record together, first the ones on db_mdone and then the ones on db_mdtwo. If you actually want them in separate places of the page you will have to to two separate queries and deal with it on your application.

So, to achieve that I suggest you add a simple integer column on each select so you can use it to order the result:

select Email, 0 as order_column from db_mdone
UNION
select Email, 1 as order_column from db_mdtwo
ORDER BY order_column

this way, all records with 0 (db_mdone) are comming first and all the record with 1 (db_mdtwo) after

Upvotes: 0

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

This is not the most efficient way of retreiving the data, but it is a start for you build upon.



<html>
<body>

<%
'=============================================================
' xxx.xxx.xxx.xxx = IP Address for the SQL Server
' yyyyyyyy = User Id for the database
' zzzzzzzz = Password for the database
'=============================================================
strWebDSN = "Provider=SQLOLEDB;server=xxx.xxx.xxx.xxx;uid=yyyyyyyy;pwd=zzzzzzzz;database=db_mdone"
Set Conn  = Server.CreateObject("ADODB.Connection")
Conn.Open strWebDSN 
set rs    = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from DATABASETABLENAME", conn

do until rs.EOF
        for each x in rs.Fields
             Response.Write(x.name)
             Response.Write(" = ")
             Response.Write(x.value & "<br />") 
        next
        Response.Write("<br />")
        rs.MoveNext
loop

rs.close
conn.close
%>

<hr>

<%
'=============================================================
' xxx.xxx.xxx.xxx = IP Address for the SQL Server
' yyyyyyyy = User Id for the database
' zzzzzzzz = Password for the database
'=============================================================
strWebDSN = "Provider=SQLOLEDB;server=xxx.xxx.xxx.xxx;uid=yyyyyyyy;pwd=zzzzzzzz;database=db_mdtwo"
Set Conn  = Server.CreateObject("ADODB.Connection")
Conn.Open strWebDSN 
set rs    = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from DATABASETABLENAME", conn

do until rs.EOF
        for each x in rs.Fields
             Response.Write(x.name)
             Response.Write(" = ")
             Response.Write(x.value & "<br />") 
        next
        Response.Write("<br />")
        rs.MoveNext
loop

rs.close
conn.close
%>


</body>
</html>

Upvotes: 0

Justin Wignall
Justin Wignall

Reputation: 3510

The basic syntax is fairly straightforward, you prefix the table name and schema with the server name e.g.

SELECT t1.Email 
FROM db_mdone.dbo.Table1 t1
INNER JOIN db_mdtwo.dbo.Table2 t2 ON t2.Email = t1.Email

(I'm not sure what you mean by 'matching up' so you'll need to work out your own actual query.

The best way to go about doing this I would say is to wrap it up in a stored procedure- you may need to use sp_addlinkeddserver to link the second server to the first assuming your connection string is to db_mdone

sp_addlinkedserver 'db_mdtwo'

Upvotes: 1

Turbot
Turbot

Reputation: 5225

How about this ?

Select Email from db_mdone.dbo.firstTable 
union all
Select Email from db_mdtwo.dbo.secondTable

Upvotes: 4

Related Questions