Compare two idetical tables and update the table

[Deven251975]

Hi,

I have two identical tables TableA, TableB.
I have the recent data in TableA because its updated and inserted with new records. Now my Question is I have to compare TableB with TableA and update TableB with TableA's data.

sugestion and code samples are appriciated.

thanks.

[frozenmist]

Hi Deven,
If you use except you will get what ever is in table a but not in b
[code=sql]
(select* from tableA) except (select * from tableB)

[/code]

If you want to update or insert from A into B then use can use MERGE command

[code=sql]
MERGE INTO TABLEB B
USING TABLEA A
ON <A's primary key> =<B's primary key>
WHEN MATCHED THEN

UPDATE SET(<B's columns>)
= (<A's Columns>)

WHEN NOT MATCHED THEN

INSERT(
<B's columns>
)
VALUES (
<A's columns>

);

[/code]

Hope this was useful
Cheers

[parikhm3]

Hi Deven,
If you use except you will get what ever is in table a but not in b
[code=sql]
(select* from tableA) except (select * from tableB)

[/code]

If you want to update or insert from A into B then use can use MERGE command

[code=sql]
MERGE INTO TABLEB B
USING TABLEA A
ON <A's primary key> =<B's primary key>
WHEN MATCHED THEN

UPDATE SET(<B's columns>)
= (<A's Columns>)

WHEN NOT MATCHED THEN

INSERT(
<B's columns>
)
VALUES (
<A's columns>

);

[/code]

Hope this was useful
Cheers

HI,
MERGE INTO WWGD_SERVICENOT ES
USING HPSTYPE

ON <A's primary key> =<B's PRIMARY key>

WHEN MATCHED THEN

UPDATE SET (<B's SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELE ASE, DATE_ACQUISITIO N, DATE_TEST, DATE_PRODUCTION , INDIVIDUAL_PROD UCTION, DATE_OBSOLETE, INDIVIDUAL_OBSO LETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar(1000)>)

= (<A's NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar(1000)>)



WHEN NOT MATCHED THEN



INSERT(<B's SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELE ASE, DATE_ACQUISITIO N, DATE_TEST, DATE_PRODUCTION , INDIVIDUAL_PROD UCTION, DATE_OBSOLETE, INDIVIDUAL_OBSO LETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE varchar(1000)> )

VALUES

( <A's NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT varchar(1000)>) ;


WOULD THIS WORK WHERE MY
TABLENAME B = WWGD_SERVICENOT ES
TABLENAME A = HPSTYPE

[parikhm3]

I AM NOT A ORACLE GUY BUT NEED TO RUN THIS AS I RUN MY OWN BUSINESS. SO PLEASE HELP OUT.


CREATE PROCEDURE [dbo].[testINS]

(

@AValue int

)

AS

DECLARE @TType nvarchar(3)

SET @TType = 'INS'

BEGIN

INSERT INTO [dbo].[OriginalTable]([aValue]) VALUES (@AValue)

INSERT INTO [dbo].[TransactionsTab le]([TType], [aValue]) VALUES (@TTYPE, @AValue)

END

GO

CREATE PROCEDURE [dbo].[testUPD]

(

@oldAValue int

@newAValue int

)

AS

DECLARE @TType nvarchar(3)

SET @TType = 'UPD'

BEGIN

UPDATE [dbo].[OriginalTable] SET [aValue] = @newAValue WHERE [aValue] = @oldAValue

INSERT INTO [dbo].[TransactionsTab le]([TType], [aValue], [newAValue]) VALUES (@TTYPE, @oldAValue, @newAValue)

END

GO
-----------------------------------------------------------

I have the above code now the thing is i don't know what to enter where
if someone could help me.


Table 1: MPTYPE

COLUMNS IN TABLE 1:

NAME, VERSION, AUTHOR, OWNER, BCREATE, BUPDATE, BREPAIR, BDELETE, ZINIT, ZCREATE, ZUPDATE, ZVERIFY, ZDELETE, ZSTOP000, ZSTOP001, ZSTOP002, COMPSIZE, HPSTYPE, REBOOT, EVENETS, ZSVCNAME, CATGROUP, DESCRIPT, ZSVCCAT


TABLE 2: WWW_SVNOTES

COLUMNS IN TABLE 2:

SERVICE_NAME, REBOOT_FLAG, BITS_AVAIL, RTP_LEVEL, ETF_FLAG, DATE_VENDORRELE ASE, DATE_ACQUISITIO N, DATE_TEST, DATE_PRODUCTION , INDIVIDUAL_PROD UCTION, DATE_OBSOLETE, INDIVIDUAL_OBSO LETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE

[parikhm3]

I figured it out but if any one want to know how here you go:

PROCEDURE p_sync_sn
AS

BEGIN

MERGE INTO WWGD_SERVICENOT ES B
USING (
SELECT SERVICE_ID, CATGROUP, DESCRIPT, REBOOT
FROM HPSTYPE
WHERE CATGROUP is NOT NULL) E
ON (E.SERVICE_ID = B.SERVICE_NAME)
WHEN MATCHED THEN
UPDATE
SET B.SVC_CATGROUP = E.CATGROUP,
B.SVC_DESCRIPT= E.DESCRIPT,
B.REBOOT_FLAG = E.REBOOT
WHEN NOT MATCHED THEN
INSERT (SERVICE_NAME, REBOOT_FLAG, SVC_CATGROUP, SVC_DESCRIPT)
VALUES (E.SERVICE_ID, E.REBOOT, E.CATGROUP, E.DESCRIPT);

commit;

exception
when others then
rollback;
raise;

end p_sync_sn;