Update script - 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

General SQL Server Forums

Script Library

Update script

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-05 : 09:11:59

Gurus
I have a scenario where i have two databases (db1 and db2) and i have two tables with same structure and with same name with DB2 having one more column with name sync..Now when any update is made in table1 of DB2 the sync column changes to 0.Now i want to update same table of the db1 with same changes where sync =0.How will i do this.?
Please help.What will be the update command which i will fire.

Regards
Nitin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 09:19:07

[code]
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
[/code]

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-05 : 09:25:56

Thanks KH
instead of hardcoding it cant i use some system table to check my columns so that i dnt have add column manually everytime i add a column or smething?
Regards
Nitin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 09:34:13

in that case you will have to use Dynamic SQL. You can obtain the column names from syscolumns or INFORMATION_SCHEMA.COLUMNS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 09:36:46

Read more on Dynamic SQL here http://www.sommarskog.se/dynamic_sql.html

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-05 : 09:36:46

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 09:38:52

quote:
Originally posted by nitin1353

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

See your other thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73082

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-05 : 09:39:24

Thanks
last question,how will i use this with my above situation?
Plz help
Regards
Nitin

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-10-05 : 09:40:35

Yes i saw your reply.How will i use this with my above said update script.
Thanks
Nitin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 09:55:00

something like this


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
*/