using System;

using System.Data.SqlClient;

using System.Text;

​

class Program

{

    static void Main()

    {

        try

        {

            MoveAndDeleteData();

        }

        catch (Exception ex)

        {

            Console.WriteLine($"Error: {ex.Message}");

        }

    }

​

    static void MoveAndDeleteData()

    {

        string connectionString = "YourConnectionString";

        string selectQuery = "SELECT * FROM MainData WHERE isDeleted = 1";

        string insertQuery = "INSERT INTO MainData_History SELECT * FROM MainData WHERE isDeleted = 1";

        string deleteQuery = "DELETE FROM MainData WHERE ID IN ({0})";

​

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            connection.Open();

​

            using (SqlTransaction transaction = connection.BeginTransaction())

            {

                try

                {

                    // Move data to MainData_History using bulk insert

                    using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection, transaction))

                    {

                        insertCommand.ExecuteNonQuery();

                    }

​

                    // Delete data from MainData in batches

                    using (SqlCommand selectCommand = new SqlCommand(selectQuery, connection, transaction))

                    using (SqlDataReader reader = selectCommand.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            int batchSize = 1000; // Adjust the batch size based on performance testing

                            int totalRows = reader.FieldCount;

​

                            for (int i = 0; i < totalRows; i += batchSize)

                            {

                                StringBuilder deleteIds = new StringBuilder();

​

                                for (int j = 0; j < batchSize && (i + j) < totalRows; j++)

                                {

                                    // Assuming the ID is the first column in the result set, adjust if needed

                                    deleteIds.Append(reader[i + j] + ",");

                                }

​

                                // Remove the trailing comma

                                if (deleteIds.Length > 0)

                                {

                                    deleteIds.Length--;

                                }

​

                                // Delete the batch

                                if (deleteIds.Length > 0)

                                {

                                    using (SqlCommand deleteCommand = new SqlCommand(string.Format(deleteQuery, deleteIds.ToString()), connection, transaction))

                                    {

                                        deleteCommand.ExecuteNonQuery();

                                    }

                                }

                            }

                        }

                    }

​

                    // Commit the transaction

                    transaction.Commit();

                }

                catch (Exception ex)

                {

                    // Rollback the transaction on error

                    transaction.Rollback();

                    throw new Exception($"Error in database operation: {ex.Message}");

                }

            }

        }

    }

}

​