December 6, 2009 at 10:58 pm
Hi All
I need help with the following,
I need split name and surname to two seperate fields but the format e.g (smith, john), (makhafula, tim). the problem is the character length is different. I've looked at CHARINDEX of which it can help but how do i put that together in a CURSOR because in to do that over 80000 records
Ta!!
It's better to fail while trying, rather than fail without trying!!!
December 6, 2009 at 11:16 pm
Hi, try this,
declare @result varchar(50)
set @result = 'smith,john'
select substring(@result,0,patindex('%,%',@result))first_col,
substring(@result,patindex('%,%',@result)+1,len(@result)) se_col
December 6, 2009 at 11:20 pm
Thanks,
Do i have to type each name that needs to be splitted?
It's better to fail while trying, rather than fail without trying!!!
December 6, 2009 at 11:26 pm
hi,
NO, this for the sample to understand with one record,
you try this way
create table #temp1
(
name1 varchar(50)
)
insert into #temp1
select 'A,AA'
union all
select 'AA,AAA'
union all
select 'AAA,AAAA'
union all
select 'B,BB'
union all
select 'BB,BBB'
union all
select 'BBB,BBBB'
select substring(name1,0,patindex('%,%',name1))first_col,
substring(name1,patindex('%,%',name1)+1,len(name1)) se_col from #temp1
December 6, 2009 at 11:49 pm
Hi Arun
I think i'm the slow one, i need to select from the source table to format the required data. Using that declare statement created early and it works but the temp table and the union statements are confussing.
Tx
It's better to fail while trying, rather than fail without trying!!!
December 6, 2009 at 11:58 pm
smthembu (12/6/2009)
but the temp table and the union statements are confussing.
Hi,
Ok, the mentioned above #temp1 table is for the example table, I don’t know the real source table from your side, so that I assume and for explain purpose I create the #temp1 table, then I insert few data again its for to explain purpose. However you catch the logic to split the string, that fine.
December 7, 2009 at 12:05 am
Thanks for your help!!
It's better to fail while trying, rather than fail without trying!!!
December 9, 2009 at 10:32 pm
Hi Arun
I have the below proc but the problem with proc when it does the insert on missing profiles step it's fails with duplicate key and i understand that, what i need is to when the record exist then it must only update what's difference. See the code below
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 SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.dbo.STG_HEAT
Where Personel_Nr=@Stage_Custid
Update SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.dbo.STG_HEAT
Where Personel_Nr=@Stage_Custid
/* Insert Missing Profiles */
Insert into SERVERNAME.DBNAME.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 DBNAME */
Declare D_Check_Exists_B Cursor Fast_forward for
Select Custid from SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.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
Thanks
It's better to fail while trying, rather than fail without trying!!!
December 9, 2009 at 11:05 pm
Hi Arun
I have the below proc but the problem with proc when it does the insert on missing profiles step it's fails with duplicate key and i understand that, what i need is to when the record exist then it must only update what's difference. See the code below
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 SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.dbo.STG_HEAT
Where Personel_Nr=@Stage_Custid
Update SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.dbo.STG_HEAT
Where Personel_Nr=@Stage_Custid
/* Insert Missing Profiles */
Insert into SERVERNAME.DBNAME.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 DBNAME */
Declare D_Check_Exists_B Cursor Fast_forward for
Select Custid from SERVERNAME.DBNAME.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 SERVERNAME.DBNAME.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
Thanks
It's better to fail while trying, rather than fail without trying!!!
December 9, 2009 at 11:27 pm
Hi,
Post this query in the new thread.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply