Execute select statement before inserting data into table

Analyst_SQL 3,551 Reputation points
2023-05-30T12:52:55.65+00:00
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);


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,265 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,860 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2023-05-31T02:22:00.51+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful