none
insert rows in million in batch

    Question

  • hi

    i want do something liek this

    declare @max int

    select @max = count(*) from table1 a join table2 b on a.id = b.id and a.date>getdate()

    while(@max)

    insert into table c

    column1,

    column2

    select * from 

     table1 a join table2 b on a.id = b.id and a.date>getdate()

    i dotn know exacxt logic, buy i have so m any rows and i need to do it on batch and faster

    Tuesday, July 16, 2013 8:18 PM

Answers

  • Hi tsql_new,

    From your codes, I found you are trying to insert duplicate records into a table, this will cost many resources and time. I suggest adding a new column stands for record repetitions, for example, we can add a column [repetitions], and use the following command to insert record into table c:

    declare @max int
    select @max = count(*) from table1 a join table2 b on a.id = b.id and a.date>getdate()
    insert into tablec(column1,column2,[repetitions])
    select column1,column2,@max from 
     table1 a join table2 b on a.id = b.id and a.date>getdate()


    Allen Li
    TechNet Community Support

    Thursday, July 18, 2013 1:25 AM

All replies

  • Here is an example for batching large transactions:

    http://www.sqlusa.com/bestpractices2005/hugeupdate/

    Well, actually batching is slower, however, blocking can be made reasonable. Between the batches other transactions can complete.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Tuesday, July 16, 2013 8:34 PM
  • Hi tsql_new,

    From your codes, I found you are trying to insert duplicate records into a table, this will cost many resources and time. I suggest adding a new column stands for record repetitions, for example, we can add a column [repetitions], and use the following command to insert record into table c:

    declare @max int
    select @max = count(*) from table1 a join table2 b on a.id = b.id and a.date>getdate()
    insert into tablec(column1,column2,[repetitions])
    select column1,column2,@max from 
     table1 a join table2 b on a.id = b.id and a.date>getdate()


    Allen Li
    TechNet Community Support

    Thursday, July 18, 2013 1:25 AM