bool CopyTable(string sourceTable, string targetTable, string modifier)
{
bool copyBySchema = false, okay = false;
copyModifier = modifier;
if (!SourceCmd.TableExists(sourceTable))
{
Memo("Error", string.Format("Copy {0}Tables: {1,7} Source table not found - {2}", modifier, "Error", sourceTable));
}
else if (!TargetCmd.TableExists(targetTable) && !(copyBySchema = CreateTargetTableSchema(sourceTable, targetTable)))
{
Memo("Error", string.Format("Copy {0}Tables: {1,7} Unable to create missing target table '{2}'{3}",
modifier, "ERROR",
targetTable,
!SQLHandler.HasErrors ? "" :
string.Format(". See errors below\r\n{0}", SQLHandler.Errors)));
}
else if (sourceObject.ServerID == targetObject.ServerID)
{
int count = CopyServerTables(sourceTable, targetTable);
if (okay = count >= 0)
{
Memo("Memo", string.Format("Copy {0}Tables: {1, 7:n0} Records {2} {3}",
modifier, count, targetTable, copyBySchema ? "- Created by Schema" : ""));
}
else
{
Memo("Error", string.Format("Copy {0}Tables: {1, 7} UNABLE TO COPY records to [{2}].{3}",
modifier, "ERROR", targetObject.ServerID, targetTable));
}
}
else
{
int sourceCount = 0, targetCount = 0;
CopySourceToTargetTable(sourceTable, targetTable, ref sourceCount, ref targetCount);
if (okay = (sourceCount == targetCount))
{
Memo("Memo", string.Format("Copy {0}Tables: {1, 7:n0} Records {2} {3}",
modifier, sourceCount, targetTable, copyBySchema ? "- Created by Schema" : ""));
}
else
{
Memo("Error", string.Format("Copy {0}Tables: {1, 7} {2} of {3} Records Copied To [{4}].{5} {6}{7}",
modifier, "ERROR",
targetCount == 0 ? "None" : "Only " + targetCount.ToString("N0"),
sourceCount, targetObject.ServerID, targetTable, copyBySchema ? "- Created by Schema" : "",
!SQLHandler.HasErrors ? "" : " See errors below -\r\n\r\n" + SQLHandler.Errors));
}
}
return(okay);
}
bool CreateTargetTableSchema(string sourceTable, string targetTable)
{
string text = GetSchemaDefinition(SourceCmd, sourceTable, targetTable);
//VELog.Error(string.Format("## VEDataAdminCopy.CreateTargetTableSchema: GetSchemaDefinition -> \r\n{0}\r\n", text));
if (text.Length > 0)
{
TargetCmd.Execute(text);
}
else
{
Memo("Memo", Message);
}
return(TargetCmd.TableExists(targetTable));
}
void CopySourceToTargetTable(string sourceTable, string targetTable, ref int sourceCount, ref int targetCount)
{
bool hasTargetTable = TargetCmd.TableExists(targetTable);
int ix = targetTable.IndexOf('.') + 1,
loop = 0;
string targetUserTable = targetObject.SchemaTable, // targetTable.Substring(ix),
txt = "";
Abort = false;
targetCount = 0;
int copySourceCount = sourceCount = SourceCmd.TableCount(sourceTable);
Status(string.Format("Retrieving {0:n0} Records", sourceCount));
do
{
if (!SourceCmd.OpenReader("SELECT * FROM " + sourceTable))
{
break;
}
//
// Replenish count if first pass generates 'IDENTITY_INSERT' errors
//
if (SQLHandler.TryAgain && loop++ == 0)
{
copySourceCount = sourceCount;
targetCount = 0;
SQLHandler.TryAgain = false;
}
try
{
string insertColumnNames = sourceCmd.ReaderInsertColumnNames,
identityInsertOn = SQLHandler.Identity && sourceCmd.ReaderHasIdentity && hasTargetTable ? "SET IDENTITY_INSERT " + targetUserTable + " ON " : "",
identityInsertOff = SQLHandler.Identity && identityInsertOn.Length > 0 ? "SET IDENTITY_INSERT " + targetUserTable + " OFF " : "";
if (hasTargetTable)
{
targetCmd.ModifyTable("DELETE " + targetUserTable);
}
Status("Copying " + sourceTable + (identityInsertOn.Length > 0 ? "(*)" : "") + " - " + copySourceCount.ToString("D0"));
StringBuilder cmd = new StringBuilder();
while (!Abort &&
copySourceCount > 0 && // Avoids duplicate key errors
!sourceCmd.ReaderEnd)
{
cmd.Length = 0;;
while (cmd.Length < 8000 && sourceCmd.ReaderRead())
{
txt = hasTargetTable
? string.Format("{0} INSERT INTO {1} {2} VALUES {3} {4} \r\n",
identityInsertOn,
targetUserTable, insertColumnNames, sourceCmd.ReaderInsertColumnValues,
identityInsertOff)
: string.Format("SELECT {0} INTO {1}\r\n", sourceCmd.ReaderObjectValues, targetUserTable);
copySourceCount--;
if (cmd.Length + txt.Length < 8000)
{
cmd.Append(txt);
}
else
{
break;
}
hasTargetTable = true; // If there was not target table, the first 'SELECT...' will have created it!
txt = "";
}
int cnt = cmd.Length > 0 ? targetCmd.ModifyTable(cmd.ToString()) : 0;
if (cnt < 0 && txt.Length == 0)
{
break;
}
targetCount += cnt;
if (txt.Length > 0)
{
if ((cnt = targetCmd.ModifyTable(txt)) < 0)
{
break;
}
else
{
targetCount += cnt;
}
}
Status(string.Format("Copying {0,8:n0} Records to {1} - {2:n0} Remaining",
sourceCount, targetTable, copySourceCount));
}
}
//catch(Exception e)
//{
// VELog.Error("VEDataAdminCopy.CopySourceToTargetTable: Exception - " + e.Message);
//}
finally
{
sourceCmd.CloseReader();
Status("");
}
}while (SQLHandler.TryAgain && !Abort);
//if(sourceCount != targetCount)
// VELog.Exception("VEDataAdmin.CopySourceToTarget: ",
// new Exception(string.Format("## {0} Copying Table: {1} of {2} Records Were Copied - copySourceCount = {3}; SQLTryAgain = {4}",
// "ERROR",
// targetCount == 0 ? "None" : "Only " + targetCount.ToString("N0"),
// sourceCount, copySourceCount, SQLTryAgain)));
}
