"Query Multiple Databases"
This search term has been used 13 times in the last week on this blog. So here is a little blog post about it
It's really not complicated to query multiple databases. You have to have permissions on both databases
The syntax looks like this:
SELECT *
FROM Database1.ObjectOwner.TableName
JOIN Database2.ObjectOwner.TableName ON.....
So here is a little script so that you can test it out
USE master
GO
--Create the first Database
CREATE DATABASE DBtest1
GO
--Create the second Database
CREATE DATABASE DBtest2
GO
USE DBtest1
GO
--Create Table1 on DBtest1
CREATE TABLE Table1(id INT, DescriptionValue VARCHAR(49))
INSERT Table1 VALUES (1,'DBtest1')
GO
USE DBtest2
GO
--Create Table2 on DBtest2
CREATE TABLE Table2(id INT, DescriptionValue VARCHAR(49))
INSERT Table2 VALUES (1,'DBtest2')
GO
USE master
GO
--Do the join between DBtest1 and DBtest2
SELECT *
FROM DBtest1.dbo.Table1 t1
JOIN DBtest2.dbo.Table2 t2 ON t1.id = t2.id
GO
--Another way
SELECT *
FROM DBtest1..Table1 t1
JOIN DBtest2..Table2 t2 ON t1.id = t2.id
GO
--Drop these databases
DROP DATABASE DBtest1,DBtest2
No comments:
Post a Comment