Insert / Update Issue

Question

I have two tables Table1 and Table 2,
I want to insert data into Table 3 from Table 1
and update Table 2

Insert into Table3(OrderNumber, Item, Code)
Select
OrderNumber,
Item,

If the item is Code is null or zero in Table 1
Insert table 3 Code with 0000000000

If the length of the code is 10 in table 1 then insert in table 3
If the length of the code is 9 in table 1 then append 0 to it and then insert In table 3
If the length of the code is less than 9, then UPDATE table2 code and status to N/ Value is less than 9

From
Table 1

Answer 1

Hi @Mansoor Mohammed ,

Welcome to Microsoft Q&A!

Please refer below:

insert into table3  
select OrderNumber,item,  
case when len(code)=9 then code+'0' when isnull(code,'')='' then '0000000000' else code end Code  
from table1  
where len(code)>=9 or isnull(code,'')=''  
  
update a  
set code=case when len(b.code)>=9 then 'Y' else 'N' end,  
Status=case when len(b.code)>=9 then 'Inserted in Table3' else 'Value is less than 9' end  
from table2 a   
inner join table1 b on a.OrderNumber=b.OrderNumber  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.