title: Copying data from one database to another - Out of Memory Exception
body: <p>I have a task where i want to copy all data from one database to another database &amp; skipping 2 tables. There are more than 200 tables.</p>
  
  <p>I have table structure ready for my 2nd databas.</p>
  
  <p>So as a solution i created a page &amp; on a button click i have below code :-</p>
  
  <pre><code>DataSet ds = new DataSet();
                  string connectionString = "Data Source=COMP112\\MSSQLSERVER2014;Initial Catalog=HCMBL;Integrated Security=True;Persist Security Info=True";
                  SqlConnection con = new SqlConnection(connectionString);
                  //render table name from database
                  string sqlTable = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_Schema='" + Session["SchemaName"].ToString() + "' and TABLE_NAME!='ENTRY' and TABLE_NAME!='OT' and TABLE_NAME!='BL_ENTRY' and TABLE_NAME!='BL_OT'";
                  con.Open();
                  SqlDataAdapter da = new SqlDataAdapter();
                  SqlCommand cmd = new SqlCommand(sqlTable, con);
                  cmd.CommandType = CommandType.Text;
                  da.SelectCommand = cmd;
                  da.Fill(ds);
                  con.Close();
                  //render connection string from WebConfig file
                  string strcon = ConfigurationManager.ConnectionStrings["SPSchema"].ConnectionString;
  
                  for (int i = 0; i &lt; ds.Tables[0].Rows.Count; i++)
                  {
                      if (!(ds.Tables[0].Rows[i]["TABLE_NAME"].ToString().Contains("Asp")))
                      {
                          string deleteQuery = "Truncate table " + Session["SchemaName"].ToString() + "." + ds.Tables[0].Rows[i]["TABLE_NAME"];
                          con.Open();
                          SqlCommand cmdDelete = new SqlCommand(deleteQuery, con);
                          cmdDelete.ExecuteNonQuery();
                          con.Close();
  
                          DataSet dataSet = new DataSet();
                          SqlConnection conn = new SqlConnection(strcon);
                          conn.Open();
                          string selectData = "select * from " + Session["SchemaName"].ToString() + "." + ds.Tables[0].Rows[i]["TABLE_NAME"];
                          SqlCommand command = new SqlCommand(selectData, conn);
                          DataTable dataTable = new DataTable();
                          SqlDataAdapter dataAdapter = new SqlDataAdapter(selectData, conn);
                          dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
                          dataAdapter.Fill(dataSet);
                          dataTable = dataSet.Tables[0];
                          conn.Close();
  
                          if (dataSet.Tables[0].Rows.Count &gt; 0)
                          {
                              //Connect to second Database and Insert row/rows.
                              SqlConnection conn2 = new SqlConnection(connectionString);
                              conn2.Open();
                              SqlBulkCopy bulkCopy = new SqlBulkCopy(conn2);
                              bulkCopy.DestinationTableName = Session["SchemaName"].ToString() + "." + ds.Tables[0].Rows[i]["TABLE_NAME"].ToString();
                              bulkCopy.WriteToServer(dataTable);
                              conn2.Close();
                          }
  
                      }
  
                  }
  </code></pre>
  
  <p>As i run the above code after inserting data in less than 10 tables, it gives out of memory exception &amp; program crashes.</p>
  
  <p>How to handle this? I tried increasing the memory capacity of SQL Server but still same error.</p>
  
  <p>Is there any other way to achieve the task?</p>