Post reply

small Transaction and insertion

  • July 8, 2008 at 3:42 am

    #77037

    All,

    I have one senario.

    I will have to pull data from one db (around 7 tables) and then insert into another database.

    Note:

    1 ) I have to do it by using SP

    2) Both database table has the same structure

    3) There is no relationships between these tables

    Method suggested(Strongly):

    stored proc should use read cursor on above tables and copy one row at a time to the corresponding tables and keep the transaction size small.

    But i feel we can simply do it like

    insert into

    select * from

    Which way is good ?

    Suggestions are welcome !

    karthik

  • July 8, 2008 at 4:37 am

    #839356

    Why do you have to use a stored proc? bcp is about the best solution to this kind of problem. DTS packages work too.

    How many rows are we talking about?

    Without a good reason for the cursored approach (if there can be one) I'd go for insert into ... select from, if it's under about 5000-10000 rows, bcp or dts if above that.

    Reason is, bcp and dts both do bulk inserts, meaning if you're in bulk logged recovery mode they operations are minimally logged and don't increase the size of the tran log that much. They can also batch the inserts keeping the size of the individual transactions down.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 8, 2008 at 6:04 am

    #839413

    I do agree with you, I am also suggested the same way.

    karthik

Viewing 3 posts - 1 through 3 (of 3 total)

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