Data Modification

[Sonia]

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end

[Chris]

Instead of using a cursor, here is a much more simplistic way to accomplish the task you have described.

INSERT INTO CompanyDetails (company_name, business_type, hardware, op_system, network, pcs, terminals)
SELECT DISTINCT
CASE
WHEN [Company Name] LIKE ('%Ltd&#39
THEN [Company Name]
WHEN [Company Name] LIKE ('%Limited&#39
THEN SUBSTRING([Company Name], 1, CHARINDEX(' Limited', [Company Name]))+'Ltd'
END 'Company Name'
[Business Type],
[Hardware],
[Operating System],
[Networks],
[PCS],
[Terminals]
FROM s_CompleteList
WHERE [Company Name] NOT IN (SELECT DISTINCT Company_Name FROM CompanyDetails)
ORDER BY 'Company Name'


NOTE: The following script doesn't update any names in the CompanyDetails Table from the Limited to Ltd. You could use the following script to update all the 'Limited' Companies in your Company Details Table:

UPDATE Company_Details
SET Company_Name = SUBSTRING([Company_Name], 1, CHARINDEX(' Limited', [Company_Name]))+'Ltd'
WHERE Company_Name LIKE ('%Limited&#39


I hope this helps...


------------
Sonia at 9/6/00 12:03:07 PM

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end

[Sonia]

Thanks Chris,
The clean up using the substring for the limited value worked. The script to input data also worked but with a couple of problems. Beacause there may be diff values in busines_type etc I have duplicate entries of the company_name when I only want one. Is there a way of merging the business_data values??

Sonia

------------
Chris at 9/6/00 5:46:00 PM

Instead of using a cursor, here is a much more simplistic way to accomplish the task you have described.

INSERT INTO CompanyDetails (company_name, business_type, hardware, op_system, network, pcs, terminals)
SELECT DISTINCT
CASE
WHEN [Company Name] LIKE ('%Ltd&#39
THEN [Company Name]
WHEN [Company Name] LIKE ('%Limited&#39
THEN SUBSTRING([Company Name], 1, CHARINDEX(' Limited', [Company Name]))+'Ltd'
END 'Company Name'
[Business Type],
[Hardware],
[Operating System],
[Networks],
[PCS],
[Terminals]
FROM s_CompleteList
WHERE [Company Name] NOT IN (SELECT DISTINCT Company_Name FROM CompanyDetails)
ORDER BY 'Company Name'


NOTE: The following script doesn't update any names in the CompanyDetails Table from the Limited to Ltd. You could use the following script to update all the 'Limited' Companies in your Company Details Table:

UPDATE Company_Details
SET Company_Name = SUBSTRING([Company_Name], 1, CHARINDEX(' Limited', [Company_Name]))+'Ltd'
WHERE Company_Name LIKE ('%Limited&#39


I hope this helps...


------------
Sonia at 9/6/00 12:03:07 PM

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end

[Chris]

Try removing the distinct and allowing the subquery to adhere to the only 1 instance rule for the name:

INSERT INTO CompanyDetails (company_name, business_type, hardware, op_system, network, pcs, terminals)
SELECT
CASE
WHEN [Company Name] LIKE ('%Ltd&#39
THEN [Company Name]
WHEN [Company Name] LIKE ('%Limited&#39
THEN SUBSTRING([Company Name], 1, CHARINDEX(' Limited', [Company Name]))+'Ltd'
END 'Company Name'
[Business Type],
[Hardware],
[Operating System],
[Networks],
[PCS],
[Terminals]
FROM s_CompleteList
WHERE [Company Name] NOT IN
(SELECT DISTINCT Company_Name FROM CompanyDetails)
ORDER BY 'Company Name'



------------
Sonia at 9/7/00 8:35:43 AM


Thanks Chris,
The clean up using the substring for the limited value worked. The script to input data also worked but with a couple of problems. Beacause there may be diff values in busines_type etc I have duplicate entries of the company_name when I only want one. Is there a way of merging the business_data values??

Sonia

------------
Chris at 9/6/00 5:46:00 PM

Instead of using a cursor, here is a much more simplistic way to accomplish the task you have described.

INSERT INTO CompanyDetails (company_name, business_type, hardware, op_system, network, pcs, terminals)
SELECT DISTINCT
CASE
WHEN [Company Name] LIKE ('%Ltd&#39
THEN [Company Name]
WHEN [Company Name] LIKE ('%Limited&#39
THEN SUBSTRING([Company Name], 1, CHARINDEX(' Limited', [Company Name]))+'Ltd'
END 'Company Name'
[Business Type],
[Hardware],
[Operating System],
[Networks],
[PCS],
[Terminals]
FROM s_CompleteList
WHERE [Company Name] NOT IN (SELECT DISTINCT Company_Name FROM CompanyDetails)
ORDER BY 'Company Name'


NOTE: The following script doesn't update any names in the CompanyDetails Table from the Limited to Ltd. You could use the following script to update all the 'Limited' Companies in your Company Details Table:

UPDATE Company_Details
SET Company_Name = SUBSTRING([Company_Name], 1, CHARINDEX(' Limited', [Company_Name]))+'Ltd'
WHERE Company_Name LIKE ('%Limited&#39


I hope this helps...


------------
Sonia at 9/6/00 12:03:07 PM

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end

[vj reddy]

hi
declare one string and then use replace command(replace(@string,''%limited',&# 39;%ltd&#39
then update. then execute the table. it will replace from limited to ltd at a time.
if u want i'll send the sql statements reg this.
ok bye
reddy


------------
Chris at 9/6/00 5:46:00 PM

Instead of using a cursor, here is a much more simplistic way to accomplish the task you have described.

INSERT INTO CompanyDetails (company_name, business_type, hardware, op_system, network, pcs, terminals)
SELECT DISTINCT
CASE
WHEN [Company Name] LIKE ('%Ltd&#39
THEN [Company Name]
WHEN [Company Name] LIKE ('%Limited&#39
THEN SUBSTRING([Company Name], 1, CHARINDEX(' Limited', [Company Name]))+'Ltd'
END 'Company Name'
[Business Type],
[Hardware],
[Operating System],
[Networks],
[PCS],
[Terminals]
FROM s_CompleteList
WHERE [Company Name] NOT IN (SELECT DISTINCT Company_Name FROM CompanyDetails)
ORDER BY 'Company Name'


NOTE: The following script doesn't update any names in the CompanyDetails Table from the Limited to Ltd. You could use the following script to update all the 'Limited' Companies in your Company Details Table:

UPDATE Company_Details
SET Company_Name = SUBSTRING([Company_Name], 1, CHARINDEX(' Limited', [Company_Name]))+'Ltd'
WHERE Company_Name LIKE ('%Limited&#39


I hope this helps...


------------
Sonia at 9/6/00 12:03:07 PM

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end