?
Solved

How do I link tables in SQL server?

Posted on 2008-10-09
8
Medium Priority
?
2,099 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
  • 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 53

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 53

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

839 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