Full Outer Join question.

Hi,
If I have two tables A and B and I am trying to insert rows, update rows and
delete rows in table B based on table A is it possible to do it one SQL
statement with a full outer Join?. I have written the SQL for getting the
data using a full outer join but want to achieve Inserting , updating and
deleting records in tableB with one statement instead of having if then
logic. wondering if there is a way.
Here is my DDL:
CREATE TABLE tblA (ProdID INT Primary key,
ProdName VARCHAR (30)
)
CREATE TABLE tblB (ProdID INT,
ProdName VARCHAR (40)
)
INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
--The following SQL gets the rows that exist in A and dont exist in B and
those that exist in B and dont exist in A.
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
--update on table A
Update tblA Set ProdName='Juices and Drinks' where prodiD=30
If i run the update on the column prodname in tblA i can rewrite my SQL as
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
OR A.ProdName <> b.ProdName
to get the products whose name are different
My goal is
1). Insert rows into tblB where they dont exist in tblA .
2). Update prodname in tblB when the prodname in tblB does not match the
prodname in tblA
3). Delete those rows in tblB which dont at all exist in tblA.
I want to accomplish this using a full outer join with one single insert and
update and delete. If thats not possible without using IF ELSE then I want
to know the best way to write less code to accomplish this.
ThanksMeher,
Why not use transactional replication to keep tblB up-to-date with tblA?
HTH
Jerry
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>|||These will work:
insert into tblB(prodId, prodName)
select prodId, prodName
from tblA
where not exists
( select *
from tblB
where tblB.prodId = tblA.prodId)
Update tblB
set prodName = tblA.prodName
from tblB
join tblA
on tblA.prodId = tblB.prodId
where tblA.prodName <> tblB.prodName
delete tblB
where not exists
( select *
from tblA
where tblB.prodId = tblA.prodId)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>