[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

link table in SQL to another SQL table

Hi,

Q:
Is it possible to link a Table from one DB in MS SQL to another table in MS SQL just like it is possible to link a SQL Table in Microsoft Access.

What I like to acheive is a contact table (on one SQL Server) feeding from serveral other contact tables in serveral DB's on different SQL Servers (Data consolidation).

Thank you!
0
LotharGores
Asked:
LotharGores
  • 5
  • 3
  • 3
  • +2
1 Solution
 
rafranciscoCommented:
Yes, it is possible.  You simply create a linked server in Enterprise Manager under the Security folder.  Right-click on Linked Servers and select New Linked Server.  From the window that will open, enter the name of the SQL Server in the Linked Server text box and select SQL Server as the Server Type.  Then on the Security tab, provide the user name and password that will be used to connect to the other server.

Hope this helps.
0
 
lluthienCommented:
yes..

if you are in db1 you can do this

select * from DB2.DBO.tableX

where db2 is the name of the other db and dbo the name of the owner of that db.

0
 
Brian CroweCommented:
I do not believe you can create a relationship between two separate databases directly but you could create triggers to inforce relational integrity between the two which is basically what a relationship is.  You could create an insert trigger in a table in one database to insure that a "key" exists in a parent table in another database before allowing the insert.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
lluthienCommented:
sorry..
didnt read the differtent servers part.

if you have different servers you do need linked servers.
0
 
LotharGoresAuthor Commented:
How do I actually link the content of my tables into my new table? I do have 3 DB's on the same server and need to link those entries into my new table - how do I do that for a start?

so when those tables change my new table change aswell!


thanks
0
 
lluthienCommented:
could you provide some information about the structure of these db's?
0
 
rafranciscoCommented:
Instead of creating a new table, why not just create a view that will consolidate your tables from the 3 databases.

CREATE VIEW dbo.AllTables
AS
SELECT * FROM DB1.dbo.Table1
UNION ALL
SELECT * FROM DB2.dbo.Table1
UNION ALL
SELECT * FROM DB3.dbo.Table1

This way, any changes done from any of the tables in the different databases will always be reflected.
0
 
LotharGoresAuthor Commented:
should I just use stored procedures to collect my data from the different DB's?
0
 
LotharGoresAuthor Commented:
rafrancisco:

Using the view, for union to work all my field have to match, some of my contact fields do match but most of them are from a  different data type, length ...

any idea to solve this?
0
 
rafranciscoCommented:
You can cast those columns that have different data types and for those missing columns, just set it to NULL, like this:

SELECT [FirstName], [LastName], [DateOfBirth]
FROM DB1.dbo.Table1
UNION ALL
SELECT [FName] AS [FirstName], [LName] AS [LastName], NULL AS [DateOfBirth] -- assuming date of birth doesn't exist
FROM DB2.dbo.Table3
UNION ALL
SELECT [First_Name] AS [FirstName], [Last_Name] AS [LastName], CAST([BirthDate] AS DATETIME)
FROM DB3.dbo.Table3
0
 
folderolCommented:
Your best strategy will depend on why you need to have the table on two servers.

I prefer using a DTS package to copy / append tables between servers.  This is not the simplest approach I think, I found DTS in SQL 2000 hard to debug.  This will become an issue if the source table changes, if for example it is part of a shrink wrap application and you upgrade.  DTS does have unique advantages in certain cases.

You can use a stored procedure that is scheduled to execute, and incorporate casting, etc to transform the columns from the source tables into 1 contact table.

If you don't need an archive, you can simply put a view on the target server, and still transform the data so that unions will work.

Exactly how is the consolidated table going to be used?
Tom
0
 
LotharGoresAuthor Commented:
Hi Tom,

I have several contact tables throughout my DB applications. All I want is single instance so that I can view all my contacts in a plain raw format. I will then run querries on this instance against certain creteria such as COUNTRY, ...

Thanks again
0
 
folderolCommented:
This sounds similar to aggregating and de-duping my lists of email addresses.  I do this with a view.  The email addresses are in different tables, with different column names, and lengths.  The view allows certain statistical reports, who is new, who has left, how many by country.

I use the stored procedure only after I determined who will be receiving the message, then the stored procedure creates a table for archive purposes of the recipients for that message.  This is static data, they were all recipients of the same message and it will never change.

Your data is likely to change, which if you copy tables, will require dropping and recreating or some sort of updating.
You may prefer the view approach, since it will always access the source tables rather than a copy that may get out of sync.

I endorse rafrancisco's approach.  Don't try to put too much logic in it, just get a flat file effect.  Then write other scripts that report from the view.

If you want a static table, simply Select * Into tablename From contactview

tom

0
 
LotharGoresAuthor Commented:
I am sorry for the late reward - rafrancisco  did get me on the right track - thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now