none
Sql Query to Insert Records from Batch wise Process

    Question

  • Hi,

    I have a table with 10 million of records and i want insert into batch wise in a existing diffent table. If i have used two method of Row by Row insert into records from existing table and use Cursor loop insert in data.But It will take to time to insert the records.so i have decide batch wise to insert 1000 records once and again 1000 records second time. so how to write query pls help..because of performance wise i have use this method.

    here my script.


    set ansi_warnings off
    go

    declare @row int
    declare @single int
    declare @time datetime
    declare @jurisID char(4)

    set @row = (select count(Rows) From Temp_SrcCodmap)
    set @single = 1--(select top 1 Rows  From Temp_SrcCodmap where Processed = 0)

    while @single <= @row
    begin
    set @time = (select getdate())
    print @time

    set @jurisID = (select JurisID from Temp_SrcCodmap where Rows = @single)

    insert HamiltonMidPolice.dbo.SrcIncAddr
    (
            IncidentIDRef
          , AddrSeqNo
          , Location
          , Premise
          , Prefix
          , StreetName
          , StreetType
          , StreetSuffix
          , AptNo
          , intPrefix
          , intStreetName
          , intStreetType
          , JurisID
    )

    select 
    incidentidref
    , ROW_NUMBER() over (PARTITION by PnxCodeValue, IncidentIDRef order by ltrim(rtrim(GAINCD)) desc) AS ADDRSEQNO
    ,Location
    ,GASTR#
    ,GASDIR
    ,GASNAM
    ,GASSUF
    ,GASDIR
    ,GASQLF
    ,GACDIR
    ,GACNAM
    ,GACSUF
    ,PnxCodeValue
    From Tem_SrcAddr where PnxCodeValue = @jurisID and Processed = 0

    update dbo.Imp_cad set Processed = 1 where Processed = @single

    set @single = @single + 1

    end


    Select * From Temp_SrcCodmap

    Wednesday, May 29, 2013 7:26 AM

Answers

  • Try SSIS or DTS to do fast data processing and batch by batch

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, May 29, 2013 7:56 AM
  • Hi,

    If you are handling huge set of records, then i would suggest to use SISS package.

    If you are using sql 2008, then create a package using DATA FLOW TASK. You have options to specify Rows per batch and Maximum insert commit size. The below link might help you.

    https://www.simple-talk.com/sql/reporting-services/using-sql-server-integration-services-to-bulk-load-data/

    If you are using sql 2000, then for DTS package.

    Thanks,
    Venkat.

    Best Regards, Venkat

    Wednesday, May 29, 2013 8:01 AM
  • Please take care of exceptions that may happen while transfering data, which i could not find in your script.

    SSIS or DTS can be an option.


    Regards, RSingh

    Wednesday, May 29, 2013 8:15 AM
  • Hi Venkat,

    Thanks for reply,

    I have Try to  SSIS Package also,  SQL Server Implementing batch processing in SSIS Package.

    It Would be take to Row by Row Process but I want insert 1000 records once and again 1000 records second time. There is not option in SSIS Package. So that is I try to SQL Query Batch Processing.

    Here for I try to Link.

    http://www.mssqltips.com/sqlservertip/1504/how-to-implement-batch-processing-in-sql-server-integration-services-ssis/

    Thanks & Regards,

    Thiru.



    Thirumurugan

    Wednesday, May 29, 2013 8:35 AM

All replies