update t1
set  col1 = t2.col1,
     col2 = t2.col2,
     . . . 
from   db1.dbo.table t1 inner join db2.dbo.table t2
       on t1.pk = t2.pk
where  t2.sync = 0

quote:

---

Originally posted by nitin1353

Hi
yes i understand this,but how will i pick up the column of a particular table?
Regards
Nitin

---

use Northwind

declare @sql 	varchar(8000)
declare	@table	varchar(50)
declare @keycol	varchar(50)

select	@table	= 'Customers',
	@keycol	= 'CustomerID'

select	@sql	= 'UPDATE t1' + char(13) + 'SET' + char(13) 
select	@sql	= @sql + char(9) + quotename(COLUMN_NAME) + ' = t2.' + quotename(COLUMN_NAME) + ',' + char(13)
from 	INFORMATION_SCHEMA.COLUMNS 
where 	TABLE_NAME 	= @table
and	COLUMN_NAME	not in ('sync', 'CustomerID')
order by ORDINAL_POSITION

-- remove the last comma + CR
select	@sql = left(@sql, len(@sql) - 2) + char(13)

select	@table	= quotename(@table),
	@keycol	= quotename(@keycol)

select	@sql	= @sql + 'FROM db1.dbo.' + @table + ' t1 INNER JOIN db2.dbo.' + @table + ' t2' + char(13)
select	@sql	= @sql + 'ON t1.' + @keycol + ' = t2.' + @keycol + char(13)
select	@sql	= @sql + 'WHERE t2.sync = 0'

--	print out to verify script
print 	@sql

--	remove to execute
--	exec(@sql)
/* OUTPUT
UPDATE t1
SET
	[CompanyName] = t2.[CompanyName],
	[ContactName] = t2.[ContactName],
	[ContactTitle] = t2.[ContactTitle],
	[Address] = t2.[Address],
	[City] = t2.[City],
	[Region] = t2.[Region],
	[PostalCode] = t2.[PostalCode],
	[Country] = t2.[Country],
	[Phone] = t2.[Phone],
	[Fax] = t2.[Fax]
FROM db1.dbo.[Customers] t1 INNER JOIN db2.dbo.[Customers] t2
ON t1.[CustomerID] = t2.[CustomerID]
WHERE t2.sync = 0
*/

quote:

---

FROM db1.dbo.[Customers] t1 INNER JOIN db2.dbo.[Customers] t2

---