Update tbl_B From tbl_A - 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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Update tbl_B From tbl_A

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-10-02 : 12:11:16

Hi all,

I have 2 tables in sql, tbl_A & tbl_B

tbl_A contains 3 columns - Ref , Company , order_no
tbl_B contains 4 Columns - Ref , Company , order_no ,Comments.

I need tbl_A to update tbl_B but to leave the comments intact.
I have a DTS package that uses the below SQL code.
When existing rows in tbl_A change then tbl_B is updated correctly.
If a new row added to tbl_A then a new row is added to tbl_B ok..

However , I am stuck on the fact that an entire row can be removed from tbl_A but it still remains in tbl_B ?????
What other bit of code do I need to catch this ??

SQl Code so far is..
insert into tbl_B(REF,Company,order_no)-->catches any new rows added
select A.REF,A.Company,A.order_no
from tbl_A A left Join tbl_B B
ON A.order_no = B.order_no
Where B.order_no Is Null

update B-->Catches any updates to existing rows in tbl_A and UpdatesB
set B.ref = A.ref
from tbl_B B Right Join tbl_A A
ON B.order_no = A.order_no

thx in advance,
Ray..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-02 : 12:37:41

If you want to delete row from Tbl_B also, when same order_no from Tbl_A is deleted, try this:

Delete Tbl_B
From Tbl_B b Left Join Tbl_A a
on b.order_no = a.order_no
where a.order_no is null

But I think it will be better if you handle all these operations as Insert/Update/Delete trigger on table Tbl_A.

Harsh Athalye
India.
"Nothing is Impossible"

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-10-03 : 05:15:25

Thx for the advice,

Do you know anywhere that I can get the code or a tutorial to help me create an Insert/Update/Delete trigger on table Tbl_A.

Thx

Ray..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-03 : 05:19:55

quote:
Originally posted by rwaldron

Thx for the advice,

Do you know anywhere that I can get the code or a tutorial to help me create an Insert/Update/Delete trigger on table Tbl_A.

Thx

Ray..

Books Online is the best source of information !!

Look at the below article also:

[url]http://www.sql-server-performance.com/nn_triggers.asp[/url]

Harsh Athalye
India.
"Nothing is Impossible"

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-10-03 : 06:21:25

Hiya,
Your code works for deletions !!
I now have 3 steps to check for inserts,updates,deletions.

tbl_A is a table that is formed from a query result that runs every minute.
For that reason I don't know if Inserts,updates,deletions are generated as the table is not being added to directly..

Is it a really a bad idea to use the code below in a stored Procedure

Any advice please.

insert into tbl_B(order_no,rep) --> This catches any new rows added and updates tbl_B
select A.order_no,A.rep
from tbl_A A left Join tbl_B B
ON A.order_no = B.order_no
Where B.order_no Is Null

update B --> This Catches any updates to existing rows in tbl_A and tbl_B
set B.rep = A.rep
from tbl_B B Right Join tbl_A A
ON B.order_no = A.order_no

Delete Tbl_B --> This Catches any rows deleted from tbl_A and removes them from tbl_B
From Tbl_B b Left Join Tbl_A a
on b.order_no = a.order_no
where a.order_no is null

Thx,
Ray.

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 06:58:40

[code]
update B --> This Catches any updates to existing rows in tbl_A and tbl_B
set B.rep = A.rep
from tbl_B B Right Join tbl_A A
ON B.order_no = A.order_no
WHERE B.rep <> A.rep -- Don't update them if they are already correct!
OR (B.rep IS NULL AND A.rep IS NOT NULL) -- You'll need this too if either A or B [rep] can be NULL
OR (B.rep IS NOT NULL AND A.rep IS NULL)
[/code]
Its probably a very pathetic point, but I do these sorts of table synchronisation tasks in the order:

DELETE
UPDATE
INSERT

in the hope that there are the fewest number of rows in the table when I do the UPDATE, and thus it runs faster ... but I expect I'm deluding myself ... there might even be a case for doing the UPDATE first as the Statistics are in their best shape before you start!

But all that apart have you considered putting a Trigger on tbl_A and having it make changes to tbl_B in real time?

Kristen

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-10-03 : 07:03:38

Hi Kristen thx for reply,

I know the way I have it works but I would like to do it the most appropriate way..

you say "have you considered putting a Trigger on tbl_A and having it make changes to tbl_B in real time? "

How do I go about this..I know a trigger is more or less a special stored procedure that is useful for insert,update and delete but will this work due to the fact the my tbl_A is produced from a results query every minute and not inserted,update,deleted directly.

Any scripts already available to do such as it must be a very common task in DB's

Ray..

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 08:11:33

"will this work due to the fact the my tbl_A is produced from a results query every minute and not inserted,update,deleted directly"

Well the way I see it is that if tbl_A has not changed the results query is going to be the same as the previous one, and therefore when a change is made to tbl_A it ought to be possible to deduce the "delta" and apply that to tbl_B.

As I understand it tbl_B is the same as tbl_A except it has an extra Comment column. So you want to Update the columns if they change to preserve the Comment column in tbl_B, but otherwise Insert new rows and Delete now-obsolete rows. A trigger can definitely do that.

Kristen

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-10-03 : 09:55:23

Hiya Kristen,
Yep taht is exactly what I am trying to do. Update columns if they Change ( Except the Comment column)
The Comment Column will be updateable from a frontend website ?..
Do you think I can stick with my code in order to this or trigger ?

You think A trigger for Insert , trigger for Delete but My code for update ?

Ray....

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 10:55:28

Well, your code works (I presume!!). However there is a lag between a change to tbl_A and that being "replicated" to tbl_B by the scheduled task. I don't know how important that is?

For a trigger I would write a single trigger FOR UPDATE, INSERT, DELETE and handle the deletes/inserts/updates according to LEFT OUTER JOINs between INSERTED & DELETED tables.

You could probably do it with a FULL OUTER JOIN.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources