Linking tables from 2 different databases

[ccampbell]

Hello,
I have kind of a sticky problem. I have a database that contains a majority of the data that I need for a new database, but I need to add a few extra tables to make the database complete. Here is the problem. I have the limitation that I cannot add additional tables to the existing database. I need to create a new database but I would like teh information in 2 tables in my new database to be the same as the information from 2 tables in my old database. Any ideas on how to do this? Is there a way to create just stand alone tables that link to a database but are not part of a database? Am I making any sense? Please help.

 

[ScottAW]

If you are wanting to link data in Table 1 from DB1 with data in Table 1 in DB2 then use the full DBname path in your SQL join statement. For Example: SELECT * FROM DB1..Table1 a JOIN DB2..Table1 b on a.linkingfield = b.linkingfield.

 

[SQLSister]

Will the databases be on the same server? If so, I just created a view using the table I wanted from the other database (use the fully qualified name). The beauty of this is you can then limit exactly what columns are available to the users if you need to.

For example, I needed to access some of the information from the Employee table in the Timesheet database as part of the Proposals database. I created the following view:

Use Proposals
CREATE VIEW dbo.ViewEmployees
AS
SELECT EmployeeFirstName, EmployeeMiddleName, EmployeeLastName, EmployeeJobTitle,
EmployeeDepartment
FROM TimeSheet.dbo.Employee