Solved

How do I link tables in SQL server?

Posted on 2008-10-09
8
2,069 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 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 46

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
 
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
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.

 

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now