Updates all field data with one unique key..

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)

Updates all field data with one unique key..

Author

Topic

blocker
Yak Posting Veteran

89 Posts

Posted - 2010-03-15 : 01:02:03

Good day guys. I just want to know on how to automatically change the name of the customer in tbl_invoice(all detailed invoices),tblbalances,with customer id 000456, when the name was changed in the tbl_customer table with the same customer id within one transact sql or trigger.

Ive done this in stored procedures but after the update, i use to create another transact sql to update to other tables which customer id is the same as updated. I want to do this within one transact sql only or trigger. Pls help.

Thank you guys for giving time..

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 01:04:46

As for as i could suggest you do using to TWO Stuffs are below.

1.OUTPUT Clause

2.Trigger.

Please have a look at this thread.Please do let us know,it is helpful.

http://visakhm.blogspot.com/search/label/Composable%20DML%20SQL%202008

blocker
Yak Posting Veteran

89 Posts

Posted - 2010-03-15 : 01:25:05

thank you for the link. but its quite complicated to me. can anybody explain this.?this code is from the link above.


INSERT INTO Table_Audit ( Column1,Column2,Column3,..)
SELECT NewColumn1, NewColumn2, NewColumn3,..
FROM
(
UPDATE t
SET t.Column1=s.Column1,
t.Column2 = s.Column2,
t.Column3 = s.Column3,
..
OUTPUT INSERTED.Column1 AS NewColumn1,
DELETED.Column1 AS OldColumn1,
INSERTED.Column2 AS NewColumn2,
DELETED.Column2 AS OldColumn2,
INSERTED.Column3 AS NewColumn3,
DELETED.Column3 AS OldColumn3 ...
FROM Table1 t
INNER JOIN SourceTable s
ON s.PK=t.FK
)t
WHERE OldColumn1 <> NewColumn1
OR OldColumn2 <> NewColumn2
...

how about triggering this event.?

pls help guys. thank you and God bless.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 01:36:43

quote:
Originally posted by blocker

thank you for the link. but its quite complicated to me. can anybody explain this.?this code is from the link above.

Did you read the link fully?..please read the link carefully and write back to us,where you are failing to understand?...This could help us to help you out..

blocker
Yak Posting Veteran

89 Posts

Posted - 2010-03-15 : 01:47:29

the first INSERT command.


INSERT INTO Table_Audit ( Column1,Column2,Column3,..)
SELECT NewColumn1, NewColumn2, NewColumn3,..

where is the value inserted for ( Column1,Column2,Column3,..)

blocker
Yak Posting Veteran

89 Posts

Posted - 2010-03-15 : 01:48:43

sorry guys..im just new to sql server. especially with this transact sql. So please understand me. Im eager to learn more to be like you guys soon and im starting now..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources