Results 1 to 6 of 6

Thread: Creating a loop

  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: Creating a loop

    Hi

    I need to create an SQL table and automatically populate it with 100,000 records (just one column).

    How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

    I imagine the statement will require Loop and While.

    This is for testing purposes.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    declare @i int
    set @i=100000
    while @i>0
    begin
    insert into table_name values (@i)
    set @i=@i-1
    end

  3. #3
    Join Date
    Oct 2004
    Posts
    13
    Much appreciated!
    I'm running this against a table I've already created and I'm getting..
    "Server: Msg 213, Level 16, State 4, Line 5
    Insert Error: Column name or number of supplied values does not match table definition."

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    can u paste the ddl for the table? how many columns does the table have?
    replace the insert with:
    insert into table_name (column_name) values (@i)

  5. #5
    Join Date
    Oct 2004
    Posts
    13
    No worries - I got it to work in the end - I created a new table with just one column and everything is fine.

    Thanks for your help amigo/amiga..

  6. #6
    Join Date
    Oct 2004
    Posts
    13
    hi Harshal
    I'm hoping you can help with a problem leading on from this. The purpose of this exercise was to measure how long two scenarios take to create the tables and insert records.

    Scenario A : Stand alone desktop
    Scenario B : Server with dual processor Xeon

    The database on each is identical - yet the Desktop took 01:07 to insert compared to the server's 06:48 !! Nearly 7 minutes!

    Any ideas what could be causing this??

    Cheers

    Samsara

Posting Permissions

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