Updating records in table from another DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Updating records in table from another DB

Author

Topic

PatrickS
Starting Member

2 Posts

Posted - 2012-02-23 : 20:03:19

I would like to write a script to update records in a Period Ending Dates table where the YearNumber = 2012. I want to update all fields in a row and I want these fields and rows updated from the same table name from a another database name. For example, DatabaseA.PeriodsTable should have 12 rows updated (all fields) with 12 rows from DatabaseB.PeriodsTable. I hope this makes sense. How do I accomplish this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 21:13:20

assuming both databases are in same server you can do like this


UPDATE a
SET a.column = b.column,
..
FROM DatabaseA.dbo.PeriodsTable a
INNER JOIN DatabaseB.PeriodsTable b
ON b.linkcolumn = a.linkcolumn
WHERE a.YearNumber = 2012

linkcolumn is column by which two tables are related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PatrickS
Starting Member

2 Posts

Posted - 2012-02-24 : 11:26:59

OK, thanks. So, I need to list each cloumn on the SET clause, I cannot use an asterisk as a wilcard? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 13:05:07

quote:
Originally posted by PatrickS

OK, thanks. So, I need to list each cloumn on the SET clause, I cannot use an asterisk as a wilcard? Thanks.

nope

for update it needs to clearly understand what columns you want to update with what value so you've to list all the columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources