locked
Loop through a table and insert variable to another table RRS feed

  • Question

  • Suppose I have create a temp table by a query. The data is filled of course. 

    create table #temp (Loc varchar(3),Week varchar(6), BegDate datetime, EndDate datetime)

    Now I want to use three of columns in each row to insert another table #Bookings without using cursor.

    create table #Bookings (bInvNum int)
    insert into #Bookings
    select distinct BookInv_Num from table1 
    where table1.Branch = @LOC 
    and Indicator = 'B'  
    and exists
    (select booknumber from table2 with (index(ndxLASTUPDATE))
    where 
     table2.lastupdate >= @BeginDATE
    and table2.lastupdate < @EndDATE
    and table2.inv_num = bookinv_num  )

    The three variables are from each row of the temp table #temp @Loc=>Loc, @BeginDate=>BegDate, @EndDate=>EndDate. Originally I used cursor to fetch the variables.

    How can I do it without using cursor?

    Thursday, December 29, 2016 9:33 PM

Answers

  • Please try joins in this ample:

    create table  table2  (inv_num int,booknumber int, lastupdate datetime)
    insert into table2 values(1,1,'2016-12-10'),(2,2,'2016-12-10'),(3,3,'2016-12-10'),(4,4,'2016-12-10')
    
    --create table #Bookings (bInvNum int)
    --insert into #Bookings
    select distinct BookInv_Num from table1 
    cross apply (select LOC,BegDate,EndDate from #temp) d(LOC,BegDate,EndDate)
    where table1.Branch = LOC 
    and Indicator  = 'B'  
    and exists
    (select booknumber from table2
    where 
     table2.lastupdate >= BegDATE
    and table2.lastupdate < EndDATE
    and table2.inv_num = bookinv_num  )
    
    --or
    select distinct BookInv_Num from table1 t1
    join #temp d on t1.Branch = d.LOC 
    join table2 t2 on t2.inv_num = t1.bookinv_num  AND t2.lastupdate >= d.BegDATE and t2.lastupdate < d.EndDATE   
    where t1.Indicator  = 'B'  
    
    --Or
    select distinct BookInv_Num from table1 t1
    join #temp d on t1.Branch = d.LOC 
    join table2 t2 on t2.inv_num = t1.bookinv_num     
    where t1.Indicator  = 'B'  
    AND t2.lastupdate >= d.BegDATE and t2.lastupdate < d.EndDATE
    
     
    
    
    
    
    
    
    drop table #temp,table1,table2

    • Marked as answer by ardmore Friday, December 30, 2016 7:42 PM
    Friday, December 30, 2016 6:34 PM

All replies

  • create table #temp (Loc varchar(3),Week varchar(6), BegDate datetime, EndDate datetime)
    insert into #temp values('1','1','2016-10-12','2016-12-12')
    ,('2','1','2016-12-12','2016-12-12'),
    ('3','1','2016-12-12','2016-12-12')
    
    create table  table1  (BookInv_Num int, Branch varchar(3),Indicator char(1))
    insert into table1 values(1,'1','B' ),(2,'1','B' ),(3,'1','B' )
    
    create table  table2  (inv_num int,booknumber int, lastupdate datetime)
    insert into table2 values(1,1,'2016-12-10'),(2,2,'2016-12-10'),(3,3,'2016-12-10'),(4,4,'2016-12-10')
    
    --create table #Bookings (bInvNum int)
    --insert into #Bookings
    select distinct BookInv_Num from table1 
    cross apply (select LOC,BegDate,EndDate from #temp) d(LOC,BegDate,EndDate)
    where table1.Branch = LOC 
    and Indicator  = 'B'  
    and exists
    (select booknumber from table2
    where 
     table2.lastupdate >= BegDATE
    and table2.lastupdate < EndDATE
    and table2.inv_num = bookinv_num  )
    
    
    drop table #temp,table1,table2

    • Proposed as answer by Naomi N Friday, December 30, 2016 3:42 AM
    • Marked as answer by ardmore Friday, December 30, 2016 12:54 PM
    • Unmarked as answer by ardmore Friday, December 30, 2016 1:05 PM
    Friday, December 30, 2016 12:05 AM
  • d(LOC,BegDate,EndDate)

    What is d here? Please indicate it.

    Friday, December 30, 2016 1:05 PM
  • It is an alias. You can name it an arbitrary name at your choice. 
    Friday, December 30, 2016 3:01 PM
  • Do I have to add something for example "as d" in the script? Because when I use it in our data access framework(C#) it doesn't recognize "d". And I am not sure why it is very slow to complete it in SQL Server 2014 Management Studio.
    Friday, December 30, 2016 3:47 PM
  • You can look up CROSS APPLY to understand the sample query posted. You can wrap this query in a stored procedure for your data access layer to access.
    Friday, December 30, 2016 4:10 PM
  • Not sure. But it does much slow then before when using cross apply. It caused the timeout in my wcf service I saw similar cases from this and another one.

    My table1 has 2412082 records and table2 has 49858897 records.



    • Edited by ardmore Friday, December 30, 2016 6:12 PM
    Friday, December 30, 2016 6:06 PM
  • Please try joins in this ample:

    create table  table2  (inv_num int,booknumber int, lastupdate datetime)
    insert into table2 values(1,1,'2016-12-10'),(2,2,'2016-12-10'),(3,3,'2016-12-10'),(4,4,'2016-12-10')
    
    --create table #Bookings (bInvNum int)
    --insert into #Bookings
    select distinct BookInv_Num from table1 
    cross apply (select LOC,BegDate,EndDate from #temp) d(LOC,BegDate,EndDate)
    where table1.Branch = LOC 
    and Indicator  = 'B'  
    and exists
    (select booknumber from table2
    where 
     table2.lastupdate >= BegDATE
    and table2.lastupdate < EndDATE
    and table2.inv_num = bookinv_num  )
    
    --or
    select distinct BookInv_Num from table1 t1
    join #temp d on t1.Branch = d.LOC 
    join table2 t2 on t2.inv_num = t1.bookinv_num  AND t2.lastupdate >= d.BegDATE and t2.lastupdate < d.EndDATE   
    where t1.Indicator  = 'B'  
    
    --Or
    select distinct BookInv_Num from table1 t1
    join #temp d on t1.Branch = d.LOC 
    join table2 t2 on t2.inv_num = t1.bookinv_num     
    where t1.Indicator  = 'B'  
    AND t2.lastupdate >= d.BegDATE and t2.lastupdate < d.EndDATE
    
     
    
    
    
    
    
    
    drop table #temp,table1,table2

    • Marked as answer by ardmore Friday, December 30, 2016 7:42 PM
    Friday, December 30, 2016 6:34 PM
  • I use the second query, it is faster. By the way, in my original script, I had index index(ndxLASTUPDATE)

    Why did you skip it? Is it on purpose? If adding it to your query, can the speed be improved? And how?


    • Edited by ardmore Friday, December 30, 2016 7:27 PM
    Friday, December 30, 2016 7:27 PM
  • Can you start your query without the hint?

    You may need different index to optimize your query but it depends on your data and settings.

    Friday, December 30, 2016 7:32 PM
  • Hello ardmore,

    Could you please test following SELECT statement,

    insert into #Bookings
    select
    distinct BookInv_Num
    from #temp t, table1 t1, table2 t2
    where
    t.Loc = t1.Branch
    and t1.Indicator = 'B'
    and t1.BookInv_Num = t2.inv_num
    and t2.lastupdate between t.BegDate and t.EndDate
    

    I hope it helps,


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Friday, December 30, 2016 7:41 PM