Inter-Database Queries

I’ve had this question come up a few times now.  “How do I copy data from one database to another?”  It’s after getting a question like that, I explain fully qualified database object names.  That’s a mouthful!  Basically it’s a fancy way of saying the “full name” of a database object, like a table or view.  In order to copy data from one database to another, you will open a query window and write:

INSERT INTO database1.dbo.table1
SELECT *
FROM database2.dbo.table2

Basically whenever you refer to an object you can refer to the databasename.owner.  99% of the time the owner is dbo, which stands for database owner.  This is the only thing you have to learn to work in multiple databases at once.  All the other rules you’ve learned about working in SQL remain.  It’s just now you have access to one database from another.

This question is usually followed several months later by another question.  “Can I work with data on two different servers?”  First I usually start with “maybe.”  But usually the answer is “yes.”  The maybe is, has your Database Administrator set up trust between the servers?  The easiest way to find out, other than asking your Database Administrator, is to run:

EXEC sp_linkedservers

If this lists the server you want to connect to, then you are in business!  If it doesn’t, then you’ll have to ask your administrator to run sp_addlinkedserver.  This procedure will set up a trust relationship between the current server, and the server you name.  Once this is completed, you can go one step further with your fully qualified database object names.  If we were to return to our previous example, we can add one more piece to the object names.

INSERT INTO databaseServer1.database1.dbo.table1
SELECT *
FROM DatabaseServer2.database2.dbo.table2

That’s it.  All you need to know to get started working with multiple databases at the same time.  If you have any questions, please send them in!  I’m here to help you learn all you want about Microsoft SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *