Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: Inserting multiple records using a @start and @count with one INSERT (and no loop)

    Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.

    For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...

    declare @card_start dec(16)
    set @card_start = '1234000012340000'
    declare @card_count int
    set @card_count = 100

    drop table card_table

    create table card_table (
    card_number dec(16),
    activated char default 'N'
    )

    insert into card_table
    select
    ... ???? ....

    But WITHOUT using a while-loop (or any other kind of loop). I'm looking for fast and efficient code! Thanks.

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Code:
    INSERT		Card_Table
    		(
    			Card_Number
    		)
    SELECT		@Card_Start + Number
    FROM		master..spt_values
    WHERE		Type = 'p'
    		AND Number < @Card_Count
    This will work for @Card_Count up to 2047 for MS SQL Server 2005.
    Up to 255 for MS SQL Server 2000.

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Thanks Peso! That was the kind of creative idea I was looking for. Unfortunately 2047 isn't high enough, I probably need to go up to max-integer. But you've got me thinking about using system tables and ROW_NUMBER(). I could just do a left-outer-join with master..spt_values on itself, that will give me a lot higher max. Thanks again!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    spt_values is a convenient system table to use. A more long term solution is some sort of numbers\ tally table. Either stored:
    http://sqljunkies.com/WebLog/amachan...bersTable.aspx
    or derived:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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