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.
4 2587
Hi Deven,
If you use except you will get what ever is in table a but not in b -
(select* from tableA) except (select * from tableB)
-
-
If you want to update or insert from A into B then use can use MERGE command -
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>
-
-
);
-
-
Hope this was useful
Cheers
Hi Deven,
If you use except you will get what ever is in table a but not in b -
(select* from tableA) except (select * from tableB)
-
-
If you want to update or insert from A into B then use can use MERGE command -
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>
-
-
);
-
-
Hope this was useful
Cheers
HI,
MERGE INTO WWGD_SERVICENOTES
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_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, 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_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, 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_SERVICENOTES
TABLENAME A = HPSTYPE
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].[TransactionsTable]([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].[TransactionsTable]([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_VENDORRELEASE, DATE_ACQUISITION, DATE_TEST, DATE_PRODUCTION, INDIVIDUAL_PRODUCTION, DATE_OBSOLETE, INDIVIDUAL_OBSOLETE, SVC_COMMENT, SVC_CATGROUP, SVC_DESCRIPT, TITLE
I figured it out but if any one want to know how here you go:
PROCEDURE p_sync_sn
AS
BEGIN
MERGE INTO WWGD_SERVICENOTES 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;
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Frank Maestas |
last post by:
Hello,
I have two tables "contact_tbl" and "address_tbl". I have a query that
compares the email addresses between the two. If a match is found I
need to update a specific field in "contact_tbl"...
|
by: GitarJake |
last post by:
Hello all,
I am new to sql and have some Access experience.
In sql, how do I: compare 2 identical tables, (except for data); then update
table 1 with new data from table 2
TIA
Jake
|
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the...
|
by: Mark |
last post by:
All,
Excuse the re-post but I have found something which works for the UserID
but not for the Password (see previous post below). The problem is I don't
understand how it works and therefore...
|
by: geronimo_me |
last post by:
Hi,
I am atempting to compare part of a field with the whole of another
field in access. Is this possible?
Basically I have 2 tables with the following info:
Table1
Field1 = MR.
|
by: Riun |
last post by:
I want to check if a row in the underlying table of my form, changed, so I created a second table, pasted all rows from underlying table in second table, and then want to check if the two tables are...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
Can I code this somehow. So the code presumabley would loop through all the
tables, open each...
|
by: Edd E |
last post by:
Hi,
I have a database to store my analyses (Access 2002, WinXp), the basic structure is:
TABLE 1 = Sample Info
TABLE 2 = Analysis type 1
TABLE 3 = Analysis type 2
TABLE 4 = Analysis type 3
...
|
by: Ecohouse |
last post by:
I'm using Access 2003 and will have to separate databases with the
exact same tables and table structures.
I need to be able to compare the table from the second database
against the same table...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |