Professional Web Applications Themes

Import huge data quickly...DTS/ Asynch stored proc.... - Microsoft SQL / MS SQL Server

I have a table which contains approx 3,00,000 records. I need to import this data into another table by executing a stored procedure. This stored procedure accepts the values from the table as params. My current solution is reading the table in cursor and executing the stored procedure. This takes tooooooo long. 6 hrs. I need to make it better. Can anyone help ? Samir...

  1. #1

    Default Import huge data quickly...DTS/ Asynch stored proc....

    I have a table which contains approx 3,00,000 records. I need to
    import this data into another table by executing a stored procedure.
    This stored procedure accepts the values from the table as params. My
    current solution is reading the table in cursor and executing the
    stored procedure. This takes tooooooo long. 6 hrs. I need to make it
    better.

    Can anyone help ?

    Samir
    Samir Guest

  2. #2

    Default Re: Import huge data quickly...DTS/ Asynch stored proc....

    If you are just copying the data from one table to another, instead of
    taking the data into a cursor and iterating thru it for inserting into
    another table try this

    INSERT DestinationTable(col1,col1..)
    Select col1,col2.. from SourceTable

    HTH,
    Srinivas Sampangi


    "Samir Pandey" <com> wrote in message
    news:google.com... 


    sampangi Guest

  3. #3

    Default Re: Import huge data quickly...DTS/ Asynch stored proc....

    This example will need to be modified to include a loop so that the SELECT statement would only select maybe a 100 rows at a time. You would continue this process until you reached the end of the table. An IDENTITY column would be the best for this.


    USE Pubs
    GO

    CREATE PROCEDURE Business_sp (
    Fname varchar(30),
    Lname varchar(30)
    ) AS

    PRINT Lname + ', ' + Fname

    GO

    DECLARE Cmd varchar(8000)

    SET Cmd = ''

    -- This (concatenation) doesn't always work for all tables,
    -- forgot what the condition is for it not to. It may have to
    -- do with a particular kind of index.
    SELECT Cmd = Cmd
    + 'EXEC Business_sp '''
    + au_fname
    + ''', '''
    + REPLACE(au_lname, '''', '''''')
    + ''''
    + char(13)
    + char(10)
    FROM Authors

    --PRINT cmd
    EXEC (Cmd)

    DROP PROCEDURE Business_sp

    Mark




    On 8 Aug 2003 08:16:52 -0700, com (Samir Pandey) wrote:
     [/ref]


    Mark Guest

Similar Threads

  1. DBI problem : How can I load quickly one huge table with DBI ??.
    By Tim Haynes in forum PERL Miscellaneous
    Replies: 3
    Last Post: September 13th, 03:43 AM
  2. Replies: 1
    Last Post: July 15th, 01:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139