Execute select statement before inserting data into table

Question

Create table #tbl_GRN_Detail (GRN_ID int,Item_Code int,Item_Weight int,Item_Qty int,G_DID varchar(50))

insert into #tbl_GRN_Detail values (1111,1,5000,5,'G-8915')
insert into #tbl_GRN_Detail values (1111,1,5000,5,'G-8916')

I am using below query for get new G_DID from table.

WITH cte AS (SELECT 1 AS G_DID
                            UNION ALL
                            SELECT TOP (1) stuff(G_DID, 1, 2, '') + 1 AS G_DID
                            FROM     #tbl_GRN_Detail AS t
							
                            ORDER BY G_DID DESC)
      SELECT TOP (1)  CONCAT('G-', G_DID)  AS G_DID
      FROM     cte
	  
      ORDER BY G_DID DESC

below query i am using for inserting data into table,now i want, when i execute below query ,then first get G_DID from above query and insert in each row with New Number

 
;with mycte as (

select @GRN_ID GRN_ID ,@Item_Code Item_Code,Round((@Item_Weight/@Item_Qty),0)  Item_Weight,1 as Item_Qty,@G_DID G_DID ,1 as S_no

union all
Select  GRN_ID ,Item_Code,Item_Weight,Item_Qty,G_DID,S_no+1 as S_no
from mycte where S_no<@Item_Qty)

  insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,Item_Qty,G_DID)
 select GRN_ID ,Item_Code,Item_Weight,Item_Qty,G_DID from mycte
  option (maxrecursion 365);

Answer 1

Hi @Analyst_SQL

(1) Found that you use SELECT TOP(1) ... ORDER BY ... inside the CTE, which will not return the accurate record you want. It is recommended to put the order by clause outside of CTE or use ROW_NUMBER() instead.

(2) When I execute below query, then first get G_DID from above query and insert in each row with New Number.

Try something like this:

declare @G_DID int
select @G_DID=MAX(stuff(G_DID, 1, 2, ''))+1
from #tbl_GRN_Detail
select @G_DID

;with mycte as 
(
select @GRN_ID GRN_ID 
      ,@Item_Code Item_Code
      ,Round((@Item_Weight/@Item_Qty),0)  Item_Weight
	  ,1 as Item_Qty
	  ,@G_DID G_DID 
	  ,1 as S_no
union all
Select  GRN_ID ,Item_Code,Item_Weight,Item_Qty,G_DID+1,S_no+1 as S_no
from mycte where S_no<@Item_Qty
)
insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,Item_Qty,G_DID)
select GRN_ID ,Item_Code,Item_Weight,Item_Qty,CONCAT('G-', G_DID) from mycte
option (maxrecursion 365);

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.