select
'1000' as client,
substring(ltrim(ItemNo),1,18) as hpn,
substring(ltrim(CPN),1,30) as cpn,
substring(ltrim(MPN),1,30) as mpn,
substring(ltrim(Description),1,120) as material,
substring(ltrim(Item_Typ),1,4) as mtl_type,
substring(unit,1,3) as uom,
getdate() as valid_dt,
'EN' as Language,
Remark
 into #temp from baseItem 

go

insert into GL_BCMTL_Mstr
 (Client,
HPN,
CPN,
MPN,
Material,
MTL_Type,
UoM,
Vaild_Dt,
Language,
Remark,
Mult_Language,
Consignment_Flag,
Status,
Blocked_Flag,
Deleted_Flag,
Creation_dt,
Created_By,
Update_dt,
Updated_By
)

select 
client,
hpn,
cpn,
mpn,
material,
mtl_type,
uom,
valid_dt,
'EN',
Remark,
0,
0,
'A',
0,
0,
getdate(),
'Admin',
getdate(),
'Admin'
 from #temp
go

drop table #temp
go


update GL_BCMTL_Mstr 
	set consignment_flag = (case when b.consign_flat ='X' then 1 else 0 end),
	    customer_id = substring(ltrim(b.cust_no),1,5),
	    deleted_flag = (case  when b.isDeleted ='X' then 1 else 0 end)
from GL_BCMTL_Mstr a, baseitem b where a.hpn = b.itemno
        
go