Copy values between tables - SQL Server Forums

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)

Copy values between tables

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2012-07-26 : 06:47:49

I've got two tables with the same fields like this

Active id Months Current
NULL 2 7 true
NULL 2 4 false
NULL 2 4 true

Active id Months Current
true 2 7 true
true 2 4 false
false 2 4 true

If need to update the active field in table one based on table two where the years/months/current fields match

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 06:52:44

Years?

update t1
set active = t2.active
from table1 t1
join table2 t2
on t1.months = t2.months
and t1.current = t2.current
where coalesce(t1.active,'') <> t2.active

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-07-31 : 02:08:22

Try this:


Update Ex1
Set Active = b.Active From 
	(
		Select *, Row_Number() Over (Order By (Select NULL) ) As rn From Ex1
	) As a
	JOIN
	(
		Select *, Row_Number() Over (Order By (Select NULL) ) As rn From Ex2
	) As b 
	ON a.rn = b.rn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources