Is there a way to do this?

Question

Post reply

Is there a way to do this?

Rog Saber

SSCrazy

Points: 2548
More actions
May 26, 2011 at 12:51 pm

#236941

I have TableA:
Col1...Col2...Col3...Col4...Col5
joe.....111....aaa...null....xxx
bob.....222....bbb...null....null
mac.....333....ccc...null....null
I have TableB (layout exactly like TableA):
Col1...Col2...Col3...Col4...Col5
When a record is deleted in TableA, I copy that record to TableB. If I delete 'joe' then I have:
TableA:
Col1...Col2...Col3...Col4...Col5
bob.....222....bbb...null....null
mac.....333....ccc...null....null
TableB:
Col1...Col2...Col3...Col4...Col5
joe.....111....aaa...null....xxx
Then, an insert occurs on TableA with 'joe' again, but this time with possibly some different values. So now TableA looks like this:
Col1...Col2...Col3...Col4...Col5
joe.....444....ddd...null....null
bob.....222....bbb...null....null
mac.....333....ccc...null....null
***Here's what I want to do***
Update TableA with the values on TableB where the values exist in TableB but are null in TableA for my inserted record ('joe').
So finally, TableA looks like this:
Col1...Col2...Col3...Col4...Col5
joe.....444....ddd...null....xxx
bob.....222....bbb...null....null
mac.....333....ccc...null....null
This update will be written in an insert trigger on TableA. Just wondering if there was an easy way?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

- consider an "on delete" trigger to perform your insert into tableB

- On insert in tableA, you could also use an "insert" trigger to fetch the available missing data from tableB.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

In your "after insert" trigger on Table A a MERGE statement could be used to update data in Table A from data in Table B.

MERGE BOL article: http://technet.microsoft.com/en-us/library/bb510625.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

john jin Valued Member Points: 69 More actions · Answer 3

Unless you have many source to delete tableA, I think the best way is to change the "delete" program.

For example, you have a procedure [pro_delete]to delete tableA,

in this proc, when you delete, you output the result to a table_variable_A,

Then you insert to TableB based on the table_variable_A ---And output the result to table_variable_B,

Based on the table_variable_B you can merge into table_A

Rog Saber SSCrazy Points: 2548 More actions · Answer 4

Hey Joe,

Thanks for taking the time to answer. I did some studying and really liked the isea of a MERGE as presented by a previous poster. However, I couldn't get it to compile after trying for quite some time. We really don't have anyone at work to help so that's why I'm here. But rather than come back on the forum and have someone tell me how uneducated and wrong I am and get berated for not posting using the proper standards, I think I'll just go back to the 50's.

Thanks for your encouragement but PLEASE tell me you are not in any area in charge of education. I thought the whole purpose of this forum is to help others.