February 17, 2010 at 11:21 pm
Hi All
I need help with the below SP, there's a section on the SP where I insert data into a table. I need to place a check if record exists then it should skip the record. Please advise which is the best way to do this (error msg 2601)
tHANKS
Declare @Stage_Custid varchar (max),
@HEAT_Custid varchar (max),
@S_First_Name varchar (max),
@S_Last_Name varchar (max),
@S_Company varchar (max),
@S_Business_Unit varchar (max),
@S_Business_Unit_Description varchar (max),
@S_Department varchar (max),
@S_Department_Description varchar (max),
@S_Job_Code_Description varchar (max),
@S_Office varchar (max),
@S_Office_Description varchar (max),
@S_State_Business varchar (max),
@S_Cell_Number varchar (max),
@S_Work_Number varchar (max),
@S_Email_address varchar (max),
@S_Logon_Name varchar (max),
@S_Building varchar (max)
Declare D_Check_Exists_A Cursor Fast_forward for
Select distinct(Personel_Nr)
from server.DB.dbo.STG_HEAT
Open D_Check_Exists_A
Fetch Next from D_Check_Exists_A into @Stage_Custid
While @@Fetch_Status = 0
Begin
IF exists
(
Select *
from server.DB.dbo.Profile
where
Custid = @Stage_Custid and
Custtype = 'Customers' and
Custid like 'P%' and
(DO_NOT_SYNC_PSHR is null or DO_NOT_SYNC_PSHR <> 'T')
)
/* Update Existing Profiles */
Begin
Select
@S_First_Name=Employee_First_name,
@S_Last_Name=Employee_Last_Name,
@S_Department_Description=Department_Description,
@S_Cell_Number=Cell_Phone_Number,
@S_Work_Number=Work_Number,
@S_Email_Address=Email_Address,
@S_Job_Code_Description=Job_Code_Description,
@S_Business_Unit=Business_Unit,
@S_Business_Unit_Description=Business_Unit_Description,
@S_Department_Description=Department_Description,
@S_Department=Department,
@S_Office_Description=Office_Description,
@S_Office=Office,
@S_Building=Building
From
server.DB.dbo.STG_HEAT
Where
Personel_Nr=@Stage_Custid
Update server.DB.dbo.Profile
Set
LastName=@S_Last_Name,
FirstNames=@S_First_Name,
Dept=@S_Department_Description,
CellPhone=@S_Cell_Number,
Ext=@S_Work_Number,
EmailAddr=@S_Email_Address,
Designation=@S_Job_Code_Description,
BU_Desc=@S_Business_Unit_Description,
BU_Dept_Desc=@S_Department_Description,
BU_Dept_Code=@S_Department,
BU_Code=@S_Business_Unit,
BU_Region=@S_Office_Description,
BU_Region_Code=@S_Office,
Building=@S_Building
Where
Custid = @Stage_Custid
End
Else
Begin
/* Populate Variables */
Select
@S_First_Name=Employee_First_name,
@S_Last_Name=Employee_Last_Name,
@S_Department_Description=Department_Description,
@S_Cell_Number=Cell_Phone_Number,
@S_Work_Number=Work_Number,
@S_Email_Address=Email_Address,
@S_Job_Code_Description=Job_Code_Description,
@S_Business_Unit=Business_Unit,
@S_Business_Unit_Description=Business_Unit_Description,
@S_Department_Description=Department_Description,
@S_Department=Department,
@S_Office_Description=Office_Description,
@S_Office=Office,
@S_Building=Building
From
server.DB.dbo.STG_HEAT
Where
Personel_Nr=@Stage_Custid
/* Insert Missing Profiles */ ==========
Insert into server.DB.dbo.Profile
(
Custid,
Custtype,
LastName,
FirstNames,
Dept,
CellPhone,
Ext,
EmailAddr,
Designation,
BU_Desc,
BU_Dept_Desc,
BU_Dept_Code,
BU_Code,
BU_Region,
BU_Region_Code,
Building
)
Values
(
@Stage_Custid,
'Customers',
@S_Last_Name,
@S_First_Name,
@S_Department_Description,
@S_Cell_Number,
@S_Work_Number,
@S_Email_Address,
@S_Job_Code_Description,
@S_Business_Unit_Description,
@S_Department_Description,
@S_Department,
@S_Business_Unit,
@S_Office_Description,
@S_Office,
@S_Building
)
End
--Print 'Inserting '+ @Stage_Custid + ',' + @S_First_Name
Fetch Next from D_Check_Exists_A into @Stage_Custid
End
/* Stage Two - Check and Remove from HEAT */
Declare D_Check_Exists_B Cursor Fast_forward for
Select
Custid
from
server.DB.dbo.Profile
Where
Custtype = 'Customers' and
Custid like 'P%' and
(DO_NOT_SYNC_PSHR is null or
DO_NOT_SYNC_PSHR <> 'T')
Open D_Check_Exists_B
Fetch Next from D_Check_Exists_B into @HEAT_Custid
While @@Fetch_Status = 0
Begin
IF exists
(
Select *
from server.DB.dbo.STG_HEAT
where
Personel_Nr = @HEAT_Custid
)
Print null
Else
Delete from Profile
Where Custid = @HEAT_Custid
--Print 'Deleting ' + @HEAT_Custid
Fetch Next from D_Check_Exists_B into @HEAT_Custid
End
Close D_Check_Exists_A
Deallocate D_Check_Exists_A
Close D_Check_Exists_B
Deallocate D_Check_Exists_B
It's better to fail while trying, rather than fail without trying!!!
February 22, 2010 at 10:18 pm
Update P
Set -- check the column names, probably they are not correct here
LastName=S.S_Last_Name,
FirstNames=S.S_First_Name,
Dept=S.S_Department_Description,
CellPhone=S.S_Cell_Number,
Ext=S.S_Work_Number,
EmailAddr=S.S_Email_Address,
Designation=S.S_Job_Code_Description,
BU_Desc=S.S_Business_Unit_Description,
BU_Dept_Desc=S.S_Department_Description,
BU_Dept_Code=S.S_Department,
BU_Code=S.S_Business_Unit,
BU_Region=S.S_Office_Description,
BU_Region_Code=S.S_Office,
Building=S.S_Building
FROM server.DB.dbo.Profile P
INNER JOIN server.DB.dbo.STG_HEAT S ON S.Personel_Nr = P.Custid
WHERE Custtype = 'Customers' and
Custid like 'P%' and
(DO_NOT_SYNC_PSHR is null or DO_NOT_SYNC_PSHR <>'T')
/* Insert Missing Profiles */
Insert into server.DB.dbo.Profile
(
Custid,
Custtype,
LastName,
FirstNames,
Dept,
CellPhone,
Ext,
EmailAddr,
Designation,
BU_Desc,
BU_Dept_Desc,
BU_Dept_Code,
BU_Code,
BU_Region,
BU_Region_Code,
Building
)
SELECT ..... {Columns}
FROM server.DB.dbo.STG_HEAT S
WHERE NOT EXISTS (select 1 from server.DB.dbo.Profile P
where Custtype = 'Customers' and Custid like 'P%' and
(DO_NOT_SYNC_PSHR is null or DO_NOT_SYNC_PSHR <>'T')
AND S.Personel_Nr = P.Custid
)
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply