locked
How can i add multiple rows from a temp table to a table with multiple id's RRS feed

  • Question

  • How can i insert multiple rows from a temp table to a permanent table with id's from another table?

    I have a table1 with  Id's 1, 2, 3 etc

    with a query i get multiple rows returned.

    For every id i need those multiple rows put in table2.

    how to do this, i am working from this query to get the multiple rows

    Declare @Date Date = '02-01-2020' 
    
    ;with tmp(_date) as
    (
       select cast(@Date as datetime)
       union all
       select _date + 1
         from tmp
        where _date < DATEADD(d,DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@Date),0))-1) ,@Date)
    )
    Insert into dbo.RestHours2 ([Day])
    select day(_date) 'Day'
      from  tmp

    And the select query for the id's:

    SELECT CrewId
    	FROM dbo.Crew
    	WHERE Enrolled = 1
    Table2 has no union with the other tables

    Thanks in advance

    Monday, April 9, 2018 10:23 AM

Answers

  • Declare @Date Date = '02-01-2020' 
    
    ;with tmp(_date) as
    (
       select cast(@Date as datetime)
       union all
       select _date + 1
         from tmp
        where _date < DATEADD(d,DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@Date),0))-1) ,@Date)
    )
    
    SELECT day(_date) 'Day',CrewId
    FROM
    (
    SELECT CrewId
    	FROM dbo.Crew
    	WHERE Enrolled = 1
    )m
    CROSS JOIN  tmp t


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Monday, April 9, 2018 10:44 AM
    • Marked as answer by zubenubie Tuesday, April 10, 2018 7:45 AM
    Monday, April 9, 2018 10:43 AM

All replies