How do I link tables in SQL server?

Currently all my databases are in Access and the client wants to upgrade to SQL server(tables are getting too big in Access).

I have 5 separate "Land Development" databases, that have to stay separate as they are all considered "legal entities". Each of these databases links to a single contact database. This is because a contact can buy land in each one of the Land Development databases and I did not want to re-enter all contact info into each database.

In Access, I can easily 'link' the Land Development databases to the Contact database. How would I go about doing this using SQL server without having to re-design the whole project?

Any help is appreciated. Thanks.
S_HamelAsked:
Who is Participating?
 
Cvijo123Connect With a Mentor Commented:
In sql server u just use syntax databaseName.dbo.tableName

so for your solution would be

Select
*
from Land_Development1.dbo.Orders o
inner join Contact Database.dbo.Company c
on o.PartnerID = c.PartnerID

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to create a linked server.
Open SQL Server Management Studio and connect to your instance. Drop down Server Objects  and right-click on Linked Servers and choose New...
Now you'll have a popup window that I think will be easy to you to fill.
After that you can access to your linked server tables as:
LinkedServerName.TableName

Cheers
0
 
S_HamelAuthor Commented:
All the databases are in one instance.(5 Land Developments, 1 Contact). I believe the Linked Server option would mean I need another instance of SQL Server.

Is there a way to keep them all in one instance, and link the tables between the different databases?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Cvijo123Commented:
as i said in my first post u use syntax to use tables betwwen databases within same server instance:

databaseName.dbo.tableName

so in your example probably something like:

Select * from Land_Development_db1.dbo.yourTable
0
 
S_HamelAuthor Commented:
That would work if I was just querying the data and creating views. But I need to actually link the tables because I have a number of lookup tables that are in the contact database that I use in the development databases.
0
 
Cvijo123Commented:
well u can't put foreign key's or contraints across more databases but tbh i dont understand why u need to do that.

If u have lookup fieldm, then your SP for getting some lookup field from another database will have join to that another database.

If u mean link them like in Acess that when u need to enter something in field u get combobox that lookup another table then NO u cannot do that in MS SQL.

MS SQL is only database not application itself and it can't work like Access is working with lookup fields.

Mybe if u give example or be more specific what u need to do exectly?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try to create views as "SELECT * FROM DatabaseName.dbo.TableName " in a single database and then use those views as lookup table.

Good luck
0
 
S_HamelAuthor Commented:
Thanks for the help. This will solve my problem for now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.