Hello
I'm using SQL Server 2000, Windows 2000.
I'm writing a SP to check for the existence of records in a table, and to
insert or update based on this existence. In other words, I have 2 tables
- source and destination. If a record from the source table already exists
in the destination table I want to update it, if not, I want to insert the
record. At them moment I am using 'If Exists', but I don't know how to
loop through all the records in the source table and check for each one.
Because the SP finds at least one record in both tables, Exists is true and
the SP never reaches the 'Else'.
Here's what I have so far (apologies for the formatting - it's gone a bit
mad):-
CREATE PROCEDURE UpdateClient
AS
Begin
if exists(select a.MembershipNo from tblDataDest2 a, tblDataDest b
where a.MembershipNo = b.MembershipNo)
Begin
Update tblDataDest2
set NINumber = a.NINumber, Firstname = a.Firstname, Surname = a.Surname,
Title = a.Title,
Initials = a.Initials, Address1 = a.Address1, Address2 = a.Address2,
Address3 = a.Address3,
Town = a.Town, County = a.County, Postcode = a.Postcode, Location =
a.Location,
CurrentSalary = a.CurrentSalary, NRA = a.NRA, DOB = a.DOB,
StateRetirementAge = a.StateRetirementAge,
Sex = a.Sex, ServiceStartDate = a.ServiceStartDate, FullTimeIndicator =
a.FullTimeIndicator,
PlanType = a.PlanType, OldPlanBasisAtNRA5 = a.OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4 = a.OldPlanBasisAtNRA4, OldPlanBasisAtNRA3 =
a.OldPlanBasisAtNRA3,
OldPlanBasisAtNRA2 = a.OldPlanBasisAtNRA2, OldPlanBasisAtNRA1 =
a.OldPlanBasisAtNRA1,
OldPlanBasisAtNRA = a.OldPlanBasisAtNRA, OldDBBenefitAtNRA5 =
a.OldDBBenefitAtNRA5,
OldDBBenefitAtNRA4 = a.OldDBBenefitAtNRA4, OldDBBenefitAtNRA3 =
a.OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2 = a.OldDBBenefitAtNRA2, OldDBBenefitAtNRA1 =
a.OldDBBenefitAtNRA1,
OldDBBenefitAtNRA = a.OldDBBenefitAtNRA, CAREAtNRA5 = a.CAREAtNRA5,
CAREAtNRA4 = a.CAREAtNRA4, CAREAtNRA3 = a.CAREAtNRA3, CAREAtNRA2 =
a.CAREAtNRA2,
CAREAtNRA1 = a.CAREAtNRA1, CAREAtNRA = a.CAREAtNRA, DC@.11AtNRA5 =
a.DC@.11AtNRA5,
DC@.11AtNRA4 = a.DC@.11AtNRA4, DC@.11AtNRA3 = a.DC@.11AtNRA3,
DC@.11AtNRA2 = a.DC@.11AtNRA2, DC@.11AtNRA1 = a.DC@.11AtNRA1,
DC@.11AtNRA = a.DC@.11AtNRA, S2P = a.S2P, S2POld = a.S2POld, BatchID =
a.BatchID
From tblDataDest a
where tblDataDest2.MembershipNo = a.MembershipNo
End
Else
Begin
Insert into tblDataDest2 (MembershipNo, NINumber, Firstname, Surname,
Title,
Initials, Address1, Address2, Address3, Town, County, Postcode, Location,
CurrentSalary, NRA,
DOB, StateRetirementAge, Sex, ServiceStartDate, FullTimeIndicator,
PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4, OldPlanBasisAtNRA3, OldPlanBasisAtNRA2,
OldPlanBasisAtNRA1,
OldPlanBasisAtNRA, OldDBBenefitAtNRA5, OldDBBenefitAtNRA4,
OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2, OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5,
CAREAtNRA4,
CAREAtNRA3, CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3, DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID)
Select MembershipNo, NINumber, Firstname, Surname, Title, Initials,
Address1, Address2,
Address3, Town, County, Postcode, Location, CurrentSalary, NRA, DOB,
StateRetirementAge, Sex,
ServiceStartDate, FullTimeIndicator, PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4,
OldPlanBasisAtNRA3, OldPlanBasisAtNRA2, OldPlanBasisAtNRA1,
OldPlanBasisAtNRA,
OldDBBenefitAtNRA5, OldDBBenefitAtNRA4, OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2,
OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5, CAREAtNRA4,
CAREAtNRA3,
CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3,
DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID from
tblDataDest
End
End
GO
Can anyone suggest how I can tweak this in order to have it run through
each record in the source and check it against the destination?
Many thanks
DeniseWhat about updating first all rows and then add the non existing ?
UPDATE tblDataDest2
SET (YourUpdatesetlist)
FROM tblDataDest2 T1
INNER JOIN tblDataDest T2
ON T1.MembershipNo = T2.MembershipNo
and then
INSERt INTO tblDataDest2
(Columnlisthere)
SELECT (Columnlisthere)
FROM tblDataDest T1
WHERE NOT EXISTS
(
SELECT * FROM tblDataDest2 T2
WHERE T1.MembershipNo = T2.MembershipNo
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Jens. That worked perfectly. And so simple!
On 3 May 2006 03:15:25 -0700, Jens wrote:
> What about updating first all rows and then add the non existing ?
> UPDATE tblDataDest2
> SET (YourUpdatesetlist)
> FROM tblDataDest2 T1
> INNER JOIN tblDataDest T2
> ON T1.MembershipNo = T2.MembershipNo
> and then
> INSERt INTO tblDataDest2
> (Columnlisthere)
> SELECT (Columnlisthere)
> FROM tblDataDest T1
> WHERE NOT EXISTS
> (
> SELECT * FROM tblDataDest2 T2
> WHERE T1.MembershipNo = T2.MembershipNo
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Jena
I sure would like to see the DDL of the table and some sample data to go
along with that. I have a similar business requirements and would like to se
e
if I could duplicate your results. This would give me a great start.
Thanks In Advance
kw_uh97
"Denise" wrote:
> Thanks Jens. That worked perfectly. And so simple!
>
> On 3 May 2006 03:15:25 -0700, Jens wrote:
>
>|||You can contact me along the EMailadress which can be found on my site:
http://www.sqlserver2005.de
