Post reply

Efficient way to fetch data through job

  • April 20, 2010 at 5:51 am

    #221550

    Hi

    I'm using sql server 2000. I've to write a job which fetches around 10 lakhs records from a table and inserts them into other table.

    What is the best way to fetch such a huge amount of data from a table which has more than 50 lakhs records.

    Thanks in advance

    Best Regards

    Sree

  • April 21, 2010 at 7:38 am

    #1155335

    Better way would be , create a DTS package and schedule it.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 21, 2010 at 6:13 pm

    #1155787

    Thanks for your reply.

    I couldn't understand wht made the difference.

    For job also I can schedule it after working hours.

    I thought like can we write a better query or do something on the table or so on...please correct me if I'm wrong.

    Thanks in advance

    SRee

  • April 21, 2010 at 11:43 pm

    #1155855

    Sree-258588 (4/21/2010)

    I thought like can we write a better query or do something on the table

    you can execute that in batches like on every go 10000 rows will get inserted/considered.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 22, 2010 at 2:12 am

    #1155902

    Thank you for ur quick response.

    Please could you let me know how to do this in batches ... in jobs...

    SRee

  • April 22, 2010 at 3:09 am

    #1155923

    Sree-258588 (4/22/2010)

    Thank you for ur quick response.

    Please could you let me know how to do this in batches ... in jobs...

    SRee

    this is rough approach, mold it according to your requirement

    SELECT IDENTITY (INT, 1, 1) AS RowID, Table1PK

    INTO #BatchControl

    FROM table1

    INNER JOIN table3

    ON table1.col1 = table3.col1

    DECLARE @Batchsize

    SET @Batchsize = 10000

    -- WHILE there are rows left

    INSERT INTO table2 (col1, col2, col3)

    SELECT table1.col1, table1.col2, table3.col3

    FROM table1

    INNER JOIN table3

    ON table1.col1 = table3.col1

    INNER JOIN (SELECT TOP (@Batchsize) Table1PK FROM #BatchControl ORDER BY RowID) b ON b.Table1PK = table1.Table1PK

    DELETE #BatchControl

    FROM (SELECT TOP (@Batchsize) RowID FROM #BatchControl ORDER BY RowID) b WHERE b.RowID = #BatchControl.RowID

    -- END

    Here is reference http://www.sqlservercentral.com/articles/Transacions/69132/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply