USE T23H001

IF OBJECTPROPERTY (OBJECT_ID('NewSaleCh'),'ISTABLE') = 1 DROP TABLE NewSaleCh
Create Table NewSaleCh (NewChNo Varchar(255),ChallanNo Varchar(255))
insert into NewSaleCh SELECT ROW_NUMBER() Over (Order by ChallanNo) As NewChNo, ChallanNo FROM T23H001.dbo.CHALLAN_DETAIL where challanno not in (select challanno from T23H001.dbo.BILL_CHALLAN_DETAIL)
UPDATE NewSaleCh SET NewChNo = RIGHT('000000'+NewChNo,6)+'/' 

SELECT * FROM NewSaleCh 

INSERT INTO T24H001.DBO.CHALLAN_DETAIL SELECT NewSaleCh.NewChNo, '2024-04-01', '2024-04-01', CHALLAN_DETAIL.CustCode, CHALLAN_DETAIL.AgentCode, CHALLAN_DETAIL.Haste, CHALLAN_DETAIL.GrossAmt, CHALLAN_DETAIL.Remark, CHALLAN_DETAIL.TransName, CHALLAN_DETAIL.TransLRNo, CHALLAN_DETAIL.TransLRCase, '2024-04-01', CHALLAN_DETAIL.TransDespStation, CHALLAN_DETAIL.UName, CHALLAN_DETAIL.OrderForm, CHALLAN_DETAIL.Marka, CHALLAN_DETAIL.ContedBy, CHALLAN_DETAIL.ManualOrderNo, CHALLAN_DETAIL.SaleType, CHALLAN_DETAIL.PackedBy, CHALLAN_DETAIL.CheckedBy, CHALLAN_DETAIL.BuyerDetail, CHALLAN_DETAIL.Freight, CHALLAN_DETAIL.Weight, CHALLAN_DETAIL.SMAccCode FROM CHALLAN_DETAIL INNER JOIN NewSaleCh ON CHALLAN_DETAIL.ChallanNo = NewSaleCh.ChallanNo
INSERT INTO T24H001.DBO.CHALLAN_ITEM_DETAIL SELECT NewSaleCh.NewChNo, CHALLAN_ITEM_DETAIL.ItemName, CHALLAN_ITEM_DETAIL.ItemPcs, CHALLAN_ITEM_DETAIL.ItemCut, CHALLAN_ITEM_DETAIL.ItemMts, CHALLAN_ITEM_DETAIL.ItemPer, CHALLAN_ITEM_DETAIL.ItemRate, CHALLAN_ITEM_DETAIL.ItemAmount, CHALLAN_ITEM_DETAIL.ItemRangeDesp, CHALLAN_ITEM_DETAIL.ItemSrNo, CHALLAN_ITEM_DETAIL.ItemFold, CHALLAN_ITEM_DETAIL.ItemActMts, CHALLAN_ITEM_DETAIL.ItemDescription, CHALLAN_ITEM_DETAIL.OrdNo, CHALLAN_ITEM_DETAIL.OrdISrNo, CHALLAN_ITEM_DETAIL.ItemRemark, CHALLAN_ITEM_DETAIL.Bale, CHALLAN_ITEM_DETAIL.PurSrNo, CHALLAN_ITEM_DETAIL.PurISrNo, CHALLAN_ITEM_DETAIL.ItemID, CHALLAN_ITEM_DETAIL.Barcode, CHALLAN_ITEM_DETAIL.SaleMRP, CHALLAN_ITEM_DETAIL.SaleDiscPerc, CHALLAN_ITEM_DETAIL.IFSrNo FROM CHALLAN_ITEM_DETAIL INNER JOIN NewSaleCh ON CHALLAN_ITEM_DETAIL.ChallanNo = NewSaleCh.ChallanNo