?
Solved

How do I link tables in SQL server?

Posted on 2008-10-09
8
Medium Priority
?
2,081 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 1500 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 51

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 51

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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