Split Data script into parts

bruinsevebruinseve Posts: 6
edited September 15, 2005 6:35AM in SQL Toolkit Previous Versions
I'm using sql data compare(via the toolkit) to compare some moderately sized tables. When the script is generated it generates one large script for all tables with no go statements. The performance of these scripts seems to be very slow and involves passing mega byte sized scripts to sql. If I manually insert go statements the performance is greatly improved. Is there a setting which will cause sql data compare to generate subscripts with go statements ala sql compare so that I can split the statements before passing them over the wire to sql?
TIA,
Seve

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Steve,

    There is no option to split up the transactions in Data Compare. I'm sure that there must be a reason for this, but I don't know what it is at this time.
  • Actually, this may work to split the SQL into small batches. I have not tested it, though:
    SqlConnection conn=new SqlConnection("server=dataserver; initial catalog=database; user id=sa; password=xxxx; Connection Lifetime=120"); 
             conn.Open(); 
             SqlCommand cmd=new SqlCommand(); 
             cmd.Connection=conn; 
             for (int i=0; i>obExeBlock.BatchCount; i++) 
             { 
                Batch b=obExeBlock.GetBatch(i); 
                if (b.Marker==false) 
                { 
                   cmd.CommandText=b.Contents; 
                   try 
                   { 
                      cmd.ExecuteNonQuery(); 
                   } 
                   catch (SqlException e) 
                   { 
                      Console.WriteLine("SQL error: {0}", e.Message); 
                   } 
                } 
             }
    
This discussion has been closed.