-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: ulab4
Message 2 in Discussion
Hi,
Please check whether this is use full for you :
1. Create a "CITY MASTER" table which contains:
"CITY NO." Integer Auto Increment
"CITY NAME" String of Unique
"STATUS" Inserted or Update or Deleted
"Inserted Date"
"Updated Date"
"Deleted Date"
2. Create a "ADDRESS MASTER" table which contains:
"CITY NO." the primary key of "CITY MASTER" for reference
"Address"
"Phone No."
Status
Inserted Date
updated date
deleted date
3. Write a sp to insert City values in city master and make sure procedure
retruns the new inserted city no , while inserted put status as I
and inseredt date as "getdate()" for current date. similary when there is
deletion update the status as D, and list only those cites where status <> D.
4. similary we can do for Addres Master.
5. Example:
City Master
[city no] [city name] [status] [i-date] [u-date] [d-date]
1 city 1 u 01/01/07 02/01/07 null
2 city 2 d 02/01/07 null 03/01/07
3 city 3 i 04/01/07 null null
Address Master
[city no] [address] [phone] [status] [i-date] [u-date] [d-date]
1 a 11 p 11
1 a 12 p 12
1 a 13 p 13
2 a 21 p 21
2 a 22 p 22
3 a 31 p 31
please let me know is this was use full.
From: [email protected]
To: [email protected]
Subject: Stored Procedure to compare 2 tables for deletions,insertions,updations
Date: Wed, 10 Jan 2007 15:20:01 -0800
<META content="Microsoft SafeHTML" name=Generator>
<STYLE>
ExternalClass EC_ThmFgColumnHeader, .ExternalClass EC_A.FrameLink,
ExternalClass EC_A.HeaderLink, .ExternalClass EC_A.FooterLink, ExternalClass
EC_A.LgtCmd, .ExternalClass EC_A.MSNLink
{color:#FFFFFF;}
ExternalClass EC_ThmFgTitleLightBk
{color:#FF6600;}
ExternalClass EC_ThmFgSmallLight
{color:#ff0000;}
ExternalClass EC_ThmFgNavLink, .ExternalClass EC_A.NavLink, ExternalClass
EC_A.ChildLink:hover
{color:#666699;}
ExternalClass EC_ThmFgInactiveText, .ExternalClass EC_A.SystemLink
{color:#666666;}
ExternalClass EC_ThmFgFrameTitle
{color:#FFFFCC;}
ExternalClass EC_ThmFgTitleDarkBk, .ExternalClass EC_A.NavLink:hover,
ExternalClass EC_A.TitleLink
{color:#CC6600;}
ExternalClass EC_ThmFgMiscText, .ExternalClass EC_A.Cat, .ExternalClass
EC_A.SubCat
{color:#336699;}
ExternalClass EC_ThmFgCommand, .ExternalClass EC_A.Command, ExternalClass
EC_A.LargeCommand, .ExternalClass EC_A.MsgLink
{color:#003366;}
ExternalClass EC_ThmFgHeader
{color:#333333;}
ExternalClass EC_ThmFgStandard, .ExternalClass EC_A.SystemLink:hover,
ExternalClass EC_A.SubLink, .ExternalClass EC_A.ChildLink, ExternalClass
EC_A.StdLink, .ExternalClass EC_SELECT.Standard
{color:#000000;}
ExternalClass EC_ThmFgDivider
{color:#CCCCCC;}
ExternalClass EC_ThmBgStandard
{background-color:#FFFFFF;}
ExternalClass EC_ThmBgUnknown1
{background-color:#FF6600;}
ExternalClass EC_ThmBgFraming
{background-color:#666699;}
ExternalClass EC_ThmBgUnknown2
{background-color:#666666;}
ExternalClass EC_ThmBgHighlightDark
{background-color:#FFFFCC;}
ExternalClass EC_ThmBgHighlightLight, .ExternalClass #EC_idToolbar,
ExternalClass #EC_tbContents
{background-color:#FFFFE8;}
ExternalClass EC_ThmBgTitleDarkBk
{background-color:#F1F1F1;}
ExternalClass EC_ThmBgAlternate
{background-color:#ECF1F6;}
ExternalClass EC_ThmBgUnknown3
{background-color:#CCCCFF;}
ExternalClass EC_ThmBgDivider
{background-color:#CCCCCC;}
ExternalClass EC_ThmBgHeader
{background-color:#9999CC;}
ExternalClass EC_ThmBgLinks
{background-color:#8696C9;}
ExternalClass EC_ThmBgSharkBar
{background-color:#8696C9;}
ExternalClass EC_ThmBgGlobalNick
{background-color:#9394A9;}
ExternalClass EC_calfgndcolor
{color:#E00505;}
ExternalClass EC_calbgndcolor
{color:#E00505;}
</STYLE>
New Message on BDOTNET
Stored Procedure to compare 2 tables for deletions,insertions,updations
Reply
Recommend
Message 1 in Discussion
From: Dhaksagi
Hai friends,
Please help me with this cos I have been struggling with this for quite a
long time.
I need to write a stored procedure to compare 2 tables. Both the tables
consists of rows of cities with entities such as Address and Phone. So for each
city there many be n number of entities. The rows in the tables are like
City 1 C City 1
C
Address11 A Address12
A
Phone11 P Phone12
P
Address12 A
Phone 12 P
City 2 C City22
C
Address21 A Address21
A
Phone 21 P Phone 21
P
Address 22 A
Phone 22 P
The Criterias are the cities, Address, Phone - any of them can be deleted,
modified. Or inserted. I want to find the deleted rows in the Table1 and mark
them in Table 2 where they have been exactly deleted. For eg in Table 2, the
rows have a column with value 'P' or 'N' to indicated whether the previous row
or the next line has been deleted.
Some of the other conditions that prevail are
1. All the entities in a city can be deleted, and the city alone remains, in
that case the city in the Table 2 has no of entities deleted in that city
2. All the entities and the city can be deleted, so either the previous row or
the next row of the city shld have the value of deletion
The same way, the modification and insertion also, The cities can be
moved from one position to another along with their entities. that has to be
found out and mentioned in the modifications also.
So pls friends, give me some suggestions regarding this..
Thanks in Advance for ur help..
Thanks,
Dhaks
View other groups in this category.
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
Need help? If you've forgotten your password, please go to Passport Member
Services.
For other questions or feedback, go to our Contact Us page.
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
Remove my e-mail address from BDOTNET.
Call friends with PC-to-PC calling -- FREE Try it now!
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
