?
Solved

How to update columns in one table from another table in desparate databases?

Posted on 2008-11-19
2
Medium Priority
?
465 Views
Last Modified: 2012-08-14
How to update columns in one table from another table in desparate databases?
I need to update my itemBackup table column itemdesc2 from data stored in another database (web) which includes my parts table column description.

UPDATE    itemBackup
SET              itemdesc2 = Web.dbo.Parts.Description
WHERE     (Web.dbo.Resale = 'y') AND (Web.dbo.Parts.PartID COLLATE database_default IN
                          (SELECT     item COLLATE database_default
                            FROM         itemBackup))
0
Comment
Question by:dawes4000
  • 2
2 Comments
 
LVL 13

Expert Comment

by:TommyTupa
ID: 23000691
1. Go to Configuration Manager.
2. Create new SQL Alias to your server e.g. WebServer.
3. Test connection.
4. Go so SQL Server Management Studio and connect to local server.
5. Create Query:

Update itemBackup
Set ItemDesc2 = b.PartsDescription
from itemBackup a
inner join WebServer.DBName.SchemaName.Field b
on a.item = b.partid
where b.Resale = 'Y'

0
 
LVL 13

Accepted Solution

by:
TommyTupa earned 2000 total points
ID: 23021242
Oops, not that anyone will notice...but of course this is the correct syntax:
Update itemBackup
Set ItemDesc2 = b.PartsDescription
from itemBackup a
inner join WebServer.DBName.SchemaName.TableName  b
on a.item = b.partid
where b.Resale = 'Y'
0

Featured Post

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.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 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