Results 1 to 3 of 3

Thread: Query help

  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unanswered: Query help

    Hi,
    I have a single row in a table:

    Title Desc Quantity
    ------------------------------
    aaaa bbbbb 4

    and I need the query to insert "Qty" number of records into a second table, e.g

    Title1 Desc1
    -----------------
    aaaa bbbbb
    aaaa bbbbb
    aaaa bbbbb
    aaaa bbbbb


    I reckon its some sort of self join but any help would be appreciated.

    greg

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    For the example below I use a function, but you can also use any table that contains sequencial numbers with no gaps. A table with IDENTITY field that did not have any deletes would do.

    Code:
    set nocount on
    create table t1 (
       title char(4) not null, 
       [desc] varchar(50) not null, 
       quantity int not null)
    go
    create table t2 (
       title1 char(4) not null, 
       desc1 varchar(50) not null)
    go
    insert t1 values ('aaaa', 'bbbbb', 4)
    insert t1 values ('bbbb', 'ccccc', 1)
    insert t1 values ('cccc', 'ddddd', 3)
    insert t1 values ('dddd', 'eeeee', 2)
    insert t1 values ('eeee', 'fffff', 5)
    go
    insert t2
       select title, [desc] from dbo.fn_CartesianProduct() f 
          inner join t1 on f.[id] < t1.quantity order by 1
    go
    drop table t1, t2
    go

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    Excellent - I have an Integers table that substitutes nicely.
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •