Solved

How do I link tables in SQL server?

Posted on 2008-10-09
8
2,076 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:S_Hamel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 500 total points
ID: 22680107
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 22680295
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
 

Author Comment

by:S_Hamel
ID: 22689268
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 5

Expert Comment

by:Cvijo123
ID: 22689533
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
 

Author Comment

by:S_Hamel
ID: 22690483
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
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22691314
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 22701535
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
 

Author Closing Comment

by:S_Hamel
ID: 31504725
Thanks for the help. This will solve my problem for now.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question