SQL Server Performance

data sync tools

Discussion in 'Third Party Tools' started by rawkdood, Sep 13, 2005.

  1. rawkdood New Member

    I've been evaluating the demos for ApexSQL Script and Diff and am just wondering if anyone has used any other tools or methods for copying large amounts data from one server to another.

    The main table we use in our application has about 2 million rows and grows by 10-20 thousand rows per week.

    ApexSQL Script works great for generating the inserts from our source DB to be inserted into our production DB, but running a 2 million line .sql file just doesn't seem like the right approach.

    What I really want to accomplish is something I could do by using a cursor, but with that much data, it's not really efficient and I am hoping to get some better ideas about how to deal with this.

    (syntax will be wrong, but you get the idea...)
    create cursor for
    select col1, col2 from [sourcedb].[dbo].table
    begin loop
    if not exists (select col1, col2 from [destination].[dbo].table where col1 = @col1 and col2 = @col2)
    generate insert statement (insert into blah values blah)
    print statement
    end loop

    I know there is a better way, I just don't know what it is. there are constraints on the table I want so the data in col1 or col2 may not be in the destination database so I need to generate inserts. If those inserts fail, there is no harm done.

    Hope this makes sence...
  2. Madhivanan Moderator

    I think you need something like this

    Insert into Table1
    Select Columns from Table2 T2 where not exists(Select * from Table1 T1 where T1.col1=T2.col1 and T1.col2=T2.col2)

    Madhivanan

    Failing to plan is Planning to fail
  3. rawkdood New Member

    The inserts need to run 1 by 1. If I do that and the row of data already exists, the whole thing will fail if it tried to insert a duplicate row due to constraints.
  4. Madhivanan Moderator

    I think the query I suggested will avoid duplicates
    Create some dummy tables and test that query

    Madhivanan

    Failing to plan is Planning to fail

Share This Page