December 3, 2009 at 4:17 am
Hi All
I need some assistance with a cursor,
I'm pulling data from another server/table and inserting missing data in another table but the trick now is that if it finds data exist it must update if it's different from the source rather than inserting the same record. I hope the above it's clear see code below.
Tx!!
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
It's better to fail while trying, rather than fail without trying!!!
December 3, 2009 at 5:01 am
There is no need to use a cursor. You can use an update and insert statement directly on the tables. Here is a small demo that shows how to do it:
use tempdb
go
--creating the demo tables
create table source (id int, data char(1))
go
create table target (id int, data char(1))
go
--Inserting data into the tables
insert into source (id, data)
select 1, 'a'
union
select 3,'c'
union
select 4,'d'
insert into target (id, data)
select 1,'b' --should be modified
union
select 2,'b'
union
select 4,'d'
union
select 5,'e'
--Inserting the missing records according to the column id
--I use left join and whenever a key exists on the source
--table but not on the target table, it is a record that
--should be inserted as new record
insert into target (id, data)
select s.id, s.data
from source s left join target t on s.id = t.id
where t.id is null
--Updating the recods that exist on both table.
--I use inner join to get the records that exist
--on both tables
update t
set t.data = s.data
from target t inner join source s on t.id = s.id
--Check the results of both operations
select * from target
go
drop table source
go
drop table target
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2009 at 5:17 am
Tx, i will test the scenario.
It's better to fail while trying, rather than fail without trying!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply