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...
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
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.
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
