public List <TableItem> GetTableList()
{
var tables = new List <TableItem>();
var key_columns = GetPrimaryKeyColumn();
var id_columns = GetIdentityColumns();
var rows = this.RunQuery(@"
SELECT
t.Name AS TABLE_NAME,
s.NAME AS TABLE_SCHEMA,
p.rows AS RowCounts
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND p.rows > 0
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY RowCounts DESC");
foreach (var row in rows)
{
var name = "[" + row["TABLE_SCHEMA"] + "].[" + row["TABLE_NAME"] + "]";
var count = Convert.ToInt32(row["RowCounts"]);
TableItem item = new TableItem {
name = name,
count = count
};
if (id_columns.ContainsKey(name))
{
item.id_column = id_columns[name];
}
if (key_columns.ContainsKey(name))
{
item.pkey_column = key_columns[name];
}
tables.Add(item);
}
return(tables);
}
public void CopyData(TableItem item, int count)
{
SqlConnection conn1 = new SqlConnection(this.fromConnString);
conn1.Open();
SqlConnection conn2 = new SqlConnection(this.toConnString);
conn2.Open();
bool has_error = false;
int offset = 0, perpage = 1000, total = 0, duplicates = 0;
while (!has_error && total < count)
{
SqlCommand cmd1 = conn1.CreateCommand();
cmd1.CommandText = String.Format(
"SELECT * FROM {0} ORDER BY {1} DESC OFFSET {2} ROWS FETCH NEXT {3} ROWS ONLY",
item.name, item.sort_column,
offset, perpage);
// 避免打印太多进度
if (offset % 100000 == 0)
{
Console.WriteLine("offset=" + offset + "; duplicates=" + duplicates);
}
// 连续 10K 重复内容，直接退出
if (duplicates >= 10000)
{
Console.WriteLine("Too many duplicates, finishing table early");
break;
}
SqlDataReader reader = cmd1.ExecuteReader();
if (!reader.HasRows)
{
Console.WriteLine("No more rows in " + item.name + ", total read " + offset);
break;
}
while (reader.Read())
{
List <string> columns1 = new List <string>(), columns2 = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns1.Add("[" + reader.GetName(i) + "]");
columns2.Add("@" + reader.GetName(i));
}
try
{
SqlCommand cmd2 = conn2.CreateCommand();
cmd2.CommandText = String.Format(
"INSERT INTO {0} ({1}) VALUES ({2})",
item.name,
String.Join(", ", columns1.ToArray()),
String.Join(", ", columns2.ToArray())
);
if (item.id_column != null)
{
cmd2.CommandText =
"SET IDENTITY_INSERT " + item.name + " ON; " +
cmd2.CommandText +
"SET IDENTITY_INSERT " + item.name + " OFF; ";
}
for (int i = 0; i < columns2.Count; i++)
{
cmd2.Parameters.AddWithValue(columns2[i], reader.GetValue(i));
}
cmd2.ExecuteNonQuery();
duplicates = 0;
}
catch (SqlException se)
{
// duplicated key
if (se.Number == 2627)
{
duplicates++;
continue;
}
Console.WriteLine(se);
has_error = true;
break;
}
++total;
}
reader.Close();
offset += perpage;
}
Console.WriteLine("Copied " + total + " rows in total, duplicates " + duplicates);
}