Mergin two tables from different databases

Question

Hi all,

I have two tables from two different databases.

For example:

Table: Articles1 - Database: db1
Attributes: id date headline text

Table: Articles2 - Database: db2
Attributes: id date headline text

Now i want to make an article feed with articles from both tables combined and sorted by date. From my knowledge it isnt possible to make queries across two different databases?

What can i do ? Make a temporary table and insert alle articles and sort and print? Make a cronjob getting all articles?

Btw. im doing it with MS SQL and ASP.

Thanks in advance

---

After i receieved som answers i'm trying to do this:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Provider;Data Source=myDataSource;Initial Catalog=Catalog1;User Id=myID;Password=myPassword1;"
Set Conn2 = Server.CreateObject("ADODB.Connection")
Conn2.Open "Provider=Provider;Data Source=myDataSource;Initial Catalog=Catalog2;User Id=myID;Password=myPassword2;"

strSQL = "SELECT id, datetime, headline, text "&_
            "FROM Conn.dbo.vArticles "&_
            "UNION ALL "&_
            "SELECT AutoID as id, Dato as datetime, Notat as headline, Notat as text "&_
            "FROM Conn2.dbo.Notat WHERE NotatTypeID = '5'"

DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, Conn


objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

But getting some errors.

Answer 1

It is possible to query across 2 different databases. Just use 3 part name format.

SELECT id, date, headline, text 
FROM db1.dbo.Articles1
UNION ALL
SELECT id, date, headline, text 
FROM db2.dbo.Articles2

The above assumes that the databases are on the same SQL Server instance. If not you will need to set up a linked server and use 4 part name format.

Answer 2

You can link the servers to perform multi db queries

Answer 3

This is untested but if as long as the databases reside on the same host, you may be able to do something like this:

SELECT db,id,date,headline,text
FROM (
    SELECT 'db1' AS db,id,date,headline,text FROM db1.dbo.Articles1
    UNION ALL
    SELECT 'db2' AS db,id,date,headline,text FROM db2.dbo.Articles2
) tmp
ORDER BY date DESC

UPDATE:

Based on the code you have supplied:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Provider;Data Source=myDataSource;Initial Catalog=Catalog1;User Id=myID;Password=myPassword1;"

strSQL = "SELECT id, datetime, headline, text "&_
            "FROM db1.dbo.vArticles "&_
            "UNION ALL "&_
            "SELECT AutoID as id, Dato as datetime, Notat as headline, Notat as text "&_
            "FROM db2.dbo.Notat WHERE NotatTypeID = '5'"

DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, Conn

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

Where db1 and db2 are the names of your databases. I'm not used to asp so I'm not 100% sure of the syntax

There does seem to be a mistake in the variable name you call for the SQL statement. I've updated the line objRS.Open mySQL, Conn to objRS.Open strSQL, Conn