Create table #Temp
(
[excel_issuer_name] [varchar](100) ,
[cdo_issuer_name] [varchar](100) ,
[excel_asset_name] [varchar](100) ,
[cdo_facility_name] [varchar](100) ,
[Asset_securityID] [varchar](100) ,
[field_value] [varchar](100) ,
[facility_id] [int]
)
Insert into #Temp([excel_issuer_name],[cdo_issuer_name],[excel_asset_name],[cdo_facility_name],[Asset_securityID],[field_value],[facility_id])
select excel_issuer_name,cdo_issuer_name, excel_asset_name,
cdo_facility_name, Asset_securityID, field_value, facility_id from table
declare @facility_name varchar(100)
declare @issuer_name varchar(100)
declare @min int
declare @max int
select @min=Min(facility_id),@max=Max(facility_id) from tb_ceLPCFacilityID
while(@min<=@max)
begin
select @facility_name=facility_name,@issuer_name=issuer_name from cdo_suite.dbo.Facility
left join cdo_suite.dbo.issuer
on cdo_suite.dbo.Facility.issuer_id
=cdo_suite.dbo.issuer.issuer_id
where cdo_suite.dbo.Facility.facility_id=@min
update #temp set cdo_issuer_name=@issuer_name,
cdo_facility_name=@facility_name,
facility_id=@min
where asset_securityid=(select Asset_SecurityID from tb_ceLPCFacilityID where facility_id=@min)
set @min=@min+1
end
select * from #temp order by excel_issuer_name