SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Update script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  09:11:59  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 10/05/2006 :  09:19:07  Show Profile  Reply with Quote

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



KH

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  09:25:56  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/05/2006 :  09:34:13  Show Profile  Reply with Quote
in that case you will have to use Dynamic SQL. You can obtain the column names from syscolumns or INFORMATION_SCHEMA.COLUMNS


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/05/2006 :  09:36:46  Show Profile  Reply with Quote
Read more on Dynamic SQL here http://www.sommarskog.se/dynamic_sql.html


KH

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  09:36:46  Show Profile  Reply with Quote
Hi
yes i understand this,but how will i pick up the column of a particular table?
Regards
Nitin
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/05/2006 :  09:38:52  Show Profile  Reply with Quote
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


KH

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  09:39:24  Show Profile  Reply with Quote
Thanks
last question,how will i use this with my above situation?
Plz help
Regards
Nitin
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  09:40:35  Show Profile  Reply with Quote
Yes i saw your reply.How will i use this with my above said update script.
Thanks
Nitin
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/05/2006 :  09:55:00  Show Profile  Reply with Quote
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
*/



KH

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/05/2006 :  10:09:19  Show Profile  Reply with Quote
Thanks a ton.
Will this script work when we have two databases like db1 and db2.?
Regards
Nitin
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 10/05/2006 :  10:26:39  Show Profile  Reply with Quote
Yes. as long as the user has access rights to both db.

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



KH

Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 10/06/2006 :  00:06:35  Show Profile  Reply with Quote
Thanks again .One more thing can i implement this in Sql task in DTS.?
regards
Nitin
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/06/2006 :  00:12:27  Show Profile  Reply with Quote
Please do not post questions in the Script Library.

The Script Library s for posting working scripts that you are sharing.

CODO ERGO SUM
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000