none
Saving query results in temporary tables while iterating

    Question

  • Hi, 

    I am trying to save a query's result (select top 5 ISIN  from test) in a temporary table while iterating through different ISINs.  Also I would like to name the table with the name of the isin.

    This is my code that works fine when I try  not to save results.

     

     

    declare @isin as varchar(24)

     

    set rowcount 0

    select top 3 ISIN  into  isin from test  group by isin order by COUNT(*) desc

     

    set rowcount 1

    select @isin=isin from isin 

     

    while @@rowcount <>0

          begin

         

          set rowcount 0

          select top 5 ISIN  from test 

     

          --Take the new ISIN

          delete isin where ISIN = @isin

          set rowcount 1   

    select @isin = isin from isin

    end

     

    My code posted below is not correct. Any suggestion as to how I should amend it?

     

     

    declare @isin as varchar(24)

    declare @table as varchar(24)

    declare @sql1 nvarchar(4000)

    declare @sql2 nvarchar(4000)

    declare @sql3 nvarchar(4000)

     

    set rowcount 0

    select top 3 ISIN  into  isin from test  group by isin order by COUNT(*) desc

     

    set rowcount 1

    select @isin=isin from isin

    select @table=isin from isin

     

    while @@rowcount <>0

    begin

          select top 5 ISIN from Trades where ISIN=@isin

         

          set rowcount 0

          set @sql1='select top 5 ISIN into ' +@table+ ' from test'

          exec (@sql1)

         

          --Take the new ISIN

         

          set @sql2='delete isin where ISIN = ' + @isin

          exec (@sql2)

          set @sql3='set rowcount 1     select ' +@isin+ ' = isin from isin'     

          exec (@sql3)

         

    end

     

    Any help would be really appreciate it!

     

    Thanks.

    Filippo

    • Moved by Stephanie Lv Monday, August 15, 2011 4:18 AM (From:SQL Server Database Engine)
    Thursday, August 11, 2011 10:21 AM

Answers

  • Here's something to get you started

    -- sample data
    create table TEST(isin varchar(24) primary key)
    insert TEST select 'ABCDEF00001'
    insert TEST select 'ABCDEF00002'
    insert TEST select 'ABCDEF00011'
    insert TEST select 'ABCDEF00006'
    insert TEST select 'ABCDEF00017'
    insert TEST select 'ABCDEF00008'
    insert TEST select 'ABCDEF00009'
    
    -- script
    
    -- Looks like ISIN table is used as a temp. needs to be dropped if already exists for SELECT.. INTO to work
    if object_id('isin') is not null drop table isin
    ;
    declare @isin as varchar(24)
    declare @sql nvarchar(max)
    select top 3 ISIN into isin from test group by isin order by COUNT(*) desc
    ;
    select TOP(1) @isin=isin from isin -- no order by = arbitrary
    while @@rowcount > 0
    begin
    	set @sql = 'select top 5 ISIN into ' + quotename(@isin) + ' from test' -- ?? no order by = arbitrary 5
    	exec sp_executesql @sql
     
       --Take the new ISIN
       delete isin where ISIN = @isin
       select TOP(1) @isin = isin from isin
    end
    
    


    Have a good read of the comments in there.  What it actually does is create 3 tables (top 3) named according to the ISIN value, but because you are selecting from test, each table contains the exact same 5 entries.

    Note: this won't run a second time on the same data, because the tables are created using SELECT .. INTO, so if you must, use the drop table syntax shown in the same script within the dynamic sql (@sql).

    Wednesday, August 17, 2011 9:57 PM

All replies

  • I am not sure,  you are having isin  as  table name as well as column name?

    I am also not sure what you are trying to achieve? Why do you need a loop at all?

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 11, 2011 11:21 AM
  • Correct, I want to name the temporary table with the ISIN's name and the column containing the various ISINs in TEST table is called ISIN.

    I reckon that for this query a loop is not necessary, but the actual query is more complex and longer then "select top 5..." . 

    Thanks

    Thursday, August 11, 2011 11:43 AM
  • How about set based operations without a loop?

    Loop based logic in T-SQL usually (not always) is slow and scalable.

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Wednesday, August 17, 2011 9:39 PM
  • Here's something to get you started

    -- sample data
    create table TEST(isin varchar(24) primary key)
    insert TEST select 'ABCDEF00001'
    insert TEST select 'ABCDEF00002'
    insert TEST select 'ABCDEF00011'
    insert TEST select 'ABCDEF00006'
    insert TEST select 'ABCDEF00017'
    insert TEST select 'ABCDEF00008'
    insert TEST select 'ABCDEF00009'
    
    -- script
    
    -- Looks like ISIN table is used as a temp. needs to be dropped if already exists for SELECT.. INTO to work
    if object_id('isin') is not null drop table isin
    ;
    declare @isin as varchar(24)
    declare @sql nvarchar(max)
    select top 3 ISIN into isin from test group by isin order by COUNT(*) desc
    ;
    select TOP(1) @isin=isin from isin -- no order by = arbitrary
    while @@rowcount > 0
    begin
    	set @sql = 'select top 5 ISIN into ' + quotename(@isin) + ' from test' -- ?? no order by = arbitrary 5
    	exec sp_executesql @sql
     
       --Take the new ISIN
       delete isin where ISIN = @isin
       select TOP(1) @isin = isin from isin
    end
    
    


    Have a good read of the comments in there.  What it actually does is create 3 tables (top 3) named according to the ISIN value, but because you are selecting from test, each table contains the exact same 5 entries.

    Note: this won't run a second time on the same data, because the tables are created using SELECT .. INTO, so if you must, use the drop table syntax shown in the same script within the dynamic sql (@sql).

    Wednesday, August 17, 2011 9:57 PM