question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked OlafHelper-2800 commented

Store Procedure require to insert Data.

i am inserting data into database , table below

 Create table #tbl_Leaves (L_ID int,L_From_Date date,L_To_Date date,L_Days Decimal(3,1) )
 Insert into #tbl_Leaves values (1001, '2021-06-01','2021-06-05',5)


Now when above Data inserted into table, then below Data also insert into second table ,
Note: if I am giving date from 01-06-2021 to 05-06-2021 ,then it auto separate date below like

 Create table #tbl_Leaves_Details (ID int,L_ID int,L_Date date,L_qty int)
    
    
 Insert into #tbl_Leaves_Details values (1 ,1001,'2021-06-01',1)
 Insert into #tbl_Leaves_Details values (2 ,1001,'2021-06-02',1)
 Insert into #tbl_Leaves_Details values (3 ,1001,'2021-06-03',1)
 Insert into #tbl_Leaves_Details values (4 ,1001,'2021-06-04',1)
 Insert into #tbl_Leaves_Details values (5 ,1001,'2021-06-05',1)
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach commented

Good day,

Your request is not fully clear to me so I will guess a bit what you need.

I assume that you meant that each time a row is inserted to the table #tbl_Leaves then you want related rows will be automatically inserted to the table #tbl_Leaves_Details.

If this is the case, then you do not need any stored procedure but TRIGGER ON the table AFTER INSERT. Remember that You can't define DML triggers on local or global temporary tables. You will need to use simple tables like tbl_Leaves and tbl_Leaves_Details. For more information about triggers please check this document:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

In order to parse the information inserted in the tbl_Leaves you can use a calendar table - a table which include all the dates. This can be done simply by LEFT JOIN the INSERTED table with the calendar table using ON calendar.date between INSERTED.L_From_Date and INSERTED.L_To_Date




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@pituach

i am already using (table include all dates with days) or table calender.

Can't you provide me example of trigger?

0 Votes 0 ·
pituach avatar image pituach akhterhussain-3167 ·

Can't you provide me example of trigger?

Obviously I can, but the goal is to help you and not to do your work or to get virtual points for answers which will not do you got for the long range.

Can you try?
please provide your best attempt to create a trigger on the table using the after insert. you have samples in the link I provided above.

Try to re-read the explanation in my previous comment and implement the JOIN query.

We will make sure to complete the missing and that you will end wit6h a fully working solution :-)

0 Votes 0 ·
pituach avatar image pituach akhterhussain-3167 ·

By the way, please keep the discussion as a discussion and don't edit the original message but continue by responding (in comments or "answers". Note that comments are very short, so you can continue the discussion using "answer" even if this is not an answer -this is limitation of the forum system

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

Here is a part of the requested solution:

 declare @L_ID int = 1001, @from date = '20210601', @to date = '20210605'
    
 ;with cte as
     (select 1 as ID, @L_ID as L_ID, @from as L_Date, 1 as L_qty
      union all
      select cte.ID + 1, cte.L_ID, dateadd(day, 1, cte.L_Date), cte.L_qty
      from cte
      where cte.L_Date < @to)
 -- insert into ...
 select *
 from cte

P.S.: Instead posting tons of question start learning SQL

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

it is just inserting one row on executing.

 declare @L_ID int = 1001, @from date = '20210601', @to date = '20210604'
        
  ;with cte as
      (select @L_ID as L_ID, @from as L_Date, 1 as L_qty
       union all
       select  cte.L_ID, dateadd(day, 1, cte.L_Date), cte.L_qty
       from cte
       where cte.L_Date < @to)
  select *
  from cte
    
       insert into tbl_Leaves_Details (L_ID,L_Date,L_qty) values(@L_ID,@from,1)
0 Votes 0 ·

it is just inserting one row on executing.

Of course, your INSERT command insert only the scalar variable, not the result of the CTE

  declare @L_ID int = 1001, @from date = '20210601', @to date = '20210604'
            
   ;with cte as
       (select @L_ID as L_ID, @from as L_Date, 1 as L_qty
        union all
        select  cte.L_ID, dateadd(day, 1, cte.L_Date), cte.L_qty
        from cte
        where cte.L_Date < @to)
 insert into tbl_Leaves_Details (L_ID,L_Date,L_qty)  -- The INSERT comes here
 select L_ID, L_Date, L_qty
 from cte

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered pituach edited

AS @pituach mentioned, you only need an INSERT trigger on the Tbl_Leaves table. See the following code example based on your description:

 CREATE TABLE Tbl_Leaves (
     L_ID int,
     L_From_Date date,
     L_To_Date date,
     L_Days int
 );
 CREATE TABLE Tbl_Leaves_Details (
     ID int IDENTITY(1, 1) NOT NULL,
     L_ID int,
     L_Date date,
     L_Qty int
 );
 GO
    
 CREATE TRIGGER TI_Tbl_Leaves ON Tbl_Leaves
 AFTER INSERT 
 AS 
 BEGIN
     ;WITH CTE AS (
         SELECT L_ID, L_From_Date, L_To_Date
         FROM inserted
         UNION ALL
         SELECT L_ID, DATEADD(day, 1, L_From_Date) AS L_From_Date, L_To_Date
         FROM CTE
         WHERE L_From_Date < L_To_Date
     )
    
     INSERT INTO Tbl_Leaves_Details
     SELECT L_ID, L_From_Date, 1 AS L_Qty
     FROM CTE;
 END
 GO
    
 INSERT INTO Tbl_Leaves VALUES (1001, '2021-06-01', '2021-06-05', 5);
 GO
    
 SELECT * FROM Tbl_Leaves;
 SELECT * FROM Tbl_Leaves_Details;
 GO
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GuoxiongYuan-7218

You are using recursive CTE which is a type of loop. I recommend to test the use of direct approach using calendar table as I wrote above. I am pretty sure that it will provide better performance especially when you loop multiple times.

In addition using calendar table will open future options like when the OP will come back and say that he want to use only working dates or other limitations. IN calendar table it is simple to add more information and filter by any criteria we might need in the future :-)

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Well... I think that this is not a good idea to do the work of the people who come here, as our goal is to teach and help and not do their work and prevent them from learning. With that being said, seems like a discussion on different solutions started so I would like to recommend using colander table instead of looping.

GIVE A MAN a fish and you feed him for a day. Teach him how to fish and you feed him for his life time!


The trigger should simply be like this (DatesTbl is a simple table with all dates):

 CREATE OR ALTER TRIGGER TI_Tbl_Leaves ON Tbl_Leaves
 AFTER INSERT AS BEGIN
    
  INSERT INTO Tbl_Leaves_Details (L_ID, L_Date, L_Qty)
  SELECT L_ID, t2.D, 1
  FROM INSERTED t1
  LEFT JOIN DatesTbl t2 ON t2.D between L_From_Date and L_To_Date
    
 END
 GO




And here is the full sample using basic DDL+DML (can be improve by using the right indexes for example)


Step 1: preparation - create basic dates table


 CREATE TABLE DatesTbl (
  D DATE
 )
 GO
    
 INSERT DatesTbl(D)
  SELECT TOP 100000 DATEADD(DAY,ROW_Number() OVER (ORDER BY (SELECT NULL)), CONVERT(DATE,'19000101'))
  FROM sys.all_objects t1
  CROSS JOIN sys.all_objects t2
 GO
    
 CREATE CLUSTERED INDEX cix ON DatesTbl (D);
 GO

Step 2: Creating the scenario - DDL of the OP tables


 CREATE TABLE Tbl_Leaves (
     L_ID int,
     L_From_Date date,
     L_To_Date date,
     L_Days int
 );
 CREATE TABLE Tbl_Leaves_Details (
     ID int IDENTITY(1, 1) NOT NULL,
     L_ID int,
     L_Date date,
     L_Qty int
 );
 GO

Step 3: solution is simply to add trigger


 CREATE OR ALTER TRIGGER TI_Tbl_Leaves ON Tbl_Leaves
 AFTER INSERT AS BEGIN
    
  INSERT INTO Tbl_Leaves_Details (L_ID, L_Date, L_Qty)
  SELECT L_ID, t2.D, 1
  FROM INSERTED t1
  LEFT JOIN DatesTbl t2 ON t2.D between L_From_Date and L_To_Date
    
 END
 GO

Testing: adding new rows to the Tbl_Leaves table



 -- Test 1: add single row
 INSERT INTO Tbl_Leaves VALUES (1001, '2020-02-01', '2020-02-05', 5);
 GO
    
 SELECT * FROM Tbl_Leaves;
 SELECT * FROM Tbl_Leaves_Details;
 GO
    
 -- Test inserting multiple rows
 INSERT INTO Tbl_Leaves VALUES 
  (1002, '2021-03-24', '2021-03-30', 2),
  (1003, '2001-02-01', '2001-03-01', 9)
 GO
    
 SELECT * FROM Tbl_Leaves;
 SELECT * FROM Tbl_Leaves_Details;
 GO


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.