none
Inserting million of rows with help of while loop

    Question

  • In a table with 10 rows of data and five columns i need to insert a millions of rows repeating first 10 rows using while loop in sql server 

    My code is:

    DECLARE @x int,
                  @y int,
                 @Name varchar (100),
                 @Address varchar (100) 
      Select   @x = 1,
                  @y = 100,
                 @Name = 'Sri',
                 @Address = 'Hyderabad'
    WHILE @x <= @y
    BEGIN
    insert into dbo.T_NoIndex
    Select @Name + CAST(@x as varchar (100)), 
              @Address + CAST(@x as varchar (100)),0,1000
         
     Set @x = @x +1     

    END

    But I am able to insert only the first row multiple times, but i want to insert all rows of table simultaneously again with a variable

    • Edited by D S Kanth Friday, July 19, 2013 11:23 PM
    Friday, July 19, 2013 11:21 PM

Answers

  • since you want to insert the first 10 rows multiple time use 10 insert queries inside loop and run it 100,000 times which will make it million records.

    Thanks- Prajesh Please mark the post as answered if it answers your question

    • Marked as answer by D S Kanth Sunday, July 21, 2013 9:52 PM
    Friday, July 19, 2013 11:41 PM

All replies

  • since you want to insert the first 10 rows multiple time use 10 insert queries inside loop and run it 100,000 times which will make it million records.

    Thanks- Prajesh Please mark the post as answered if it answers your question

    • Marked as answer by D S Kanth Sunday, July 21, 2013 9:52 PM
    Friday, July 19, 2013 11:41 PM
  • [QUOTE] In a table with 10 rows of data and five columns I need to insert a million rows repeating first 10 rows using WHILE loop in SQL Server. [/QUOTE] 

    Tables do not have duplicate rows; this is called First Normal Form (1NF) and it is the foundation of RDBMS. I think that you want dummy data that uses a sequential number cast as a string. You also have no idea what the ISO-11179 naming standards are. 

    SQL is a declarative language; we regard WHILE, IF-THEN, recursion and other procedural code the way that vegans regard live food at a sushi bar. We keep a table, Series, of integers from 1 to some upper limit.

    INSERT INTO Clients (client_name, client_address)
    SELECT 'Sri' + CAST(seq AS VARCHAR (7)), 
            'Hyderabad' + CAST(seq AS VARCHAR (7))
      FROM Series 
     WHERE seq BETWEEN 1 AND 1000000;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 19, 2013 11:44 PM
  • Thanks Prajesh

    I want to insert 10 rows multiple rows but could not find logic for defining the variable accordingly in the while loop


    Sri

    Sunday, July 21, 2013 9:53 PM
  • --create data source, 10 sample value which will duplicate, i insert 3 value for this sample
    declare  @table table(row int,Name varchar(20),address varchar(100))
    insert into @table values (1,'Sri','Hyderabad'),(2,'Ask','Bank1'),(3,'Chang','local')
    --create desitination table
    declare @tableresult table(Name varchar(200),address varchar(200), value1 int, value2 int)
    declare @start int  --default by 1
    declare @end int    --how many time going duplication the ten rows of data
    declare @name varchar(20),@address varchar(20)
      set @start=1 
      set @end=100   
      -- i set 100 time duplication of this sample= 100x3= 300 rows will be insert into @tableresult table
      --if one million time then change to 1000000 for @end
      
      
      While @start<=@end
      BEGIN 
      declare @x int
      declare @y int
      set @x=1
      set @y=3
      while @x<=@y
      Begin
      insert into @tableresult
      Select     name + CAST(@start as varchar (100)), 
                 address + CAST(@start as varchar (100)),0,1000
                 from @table
                 where row=@x
                 set @x=@x+1          
      End  
         Set @start=@start+1         
      END           
      select * from @tableresult
    duplication 10 rows of sample, you can keying how many time you want
    Monday, July 22, 2013 2:57 AM