Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Unanswered: Multiple data insertion with For clause

    Suppose I have a table named test with two fields(sl int,age int).
    I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
    22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
    Is it possible to insert in SQL SERVER following c protype?
    What is the fastest way for inserting multiple sequevcial data?
    Subhasish

  2. #2
    Join Date
    Jul 2002
    Posts
    58

    Re: Multiple data insertion with For clause

    For relatively small sequential sets, do something like this....

    Code:
    INSERT INTO TEST(sl, age)
       SELECT a.i, a.i+21
          FROM (
             SELECT i = 1 UNION
             SELECT i = 2 UNION
             SELECT i = 3 UNION
             SELECT i = 4 UNION
             SELECT i = 5 UNION
             SELECT i = 6 UNION
             SELECT i = 7 UNION
             SELECT i = 8 UNION
             SELECT i = 9 UNION
             SELECT i = 10 ) as a
    You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

    For bigger sequential sets, build yourself a temporary table of sequential integers like this:

    Code:
    CREATE TABLE #i
    (x INT IDENTITY(1,1),
     y INT)
    
    INSERT INTO #i
       VALUES(NULL)
    
    INSERT INTO #i
       SELECT y FROM #i
    Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

    Code:
    INSERT INTO test(sl, age)
       SELECT #i.x, #i.x+21
          FROM #i
          WHERE #i.x < 100  -- for 100 rows

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think s/he's looking for a loop as well...
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (sl int,age int)
    GO
    
    DECLARE @x int, @y int
    
    SELECT @x = 1, @y = 1
    
    WHILE @x < 100
    	BEGIN
    		INSERT INTO myTable99 (sl, age)
    		SELECT @X, 1*@y UNION ALL
    		SELECT @X, 2*@y UNION ALL
    		SELECT @X, 3*@y UNION ALL
    		SELECT @X, 4*@y UNION ALL
    		SELECT @X, 5*@y UNION ALL
    		SELECT @X, 6*@y UNION ALL
    		SELECT @X, 7*@y UNION ALL
    		SELECT @X, 8*@y UNION ALL
    		SELECT @X, 9*@y UNION ALL
    		SELECT @X, 10*@y
    		
    		SELECT @x = @x + 1, @y = @y + 1
    	END
    
    SELECT COUNT(*) FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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