如何使用SMO复制数据库仅迁移指定表的数据？

Got it, let's adjust your SMO code to only transfer schema and data for specific tables instead of the entire database. Here's the step-by-step modification and updated code:

Key Changes Needed #

• Disable the "copy all tables" flag since we want to pick specific ones
• Explicitly add your target tables to the transfer's object collection
• Keep necessary schema-related settings (like constraints, indexes) for the specified tables if needed

Updated Code #

try {
    if (isFromConsole) Console.WriteLine("Initializing database creation...");
    var srcDbInfo = GetInfo(srcConnectionString);
    var destDbInfo = GetInfo(destConnectionString);
    var sc = new ServerConnection();
    sc.LoginSecure = false;
    sc.ServerInstance = srcDbInfo.DataSource;
    sc.Login = srcDbInfo.UserID;
    sc.Password = srcDbInfo.Password;
    sc.ConnectTimeout = 0;
    sc.StatementTimeout = 0;
    sc.Connect();
    if (sc.IsOpen) {
        Server server = new Server(sc);
        Database srcDb = server.Databases[srcDbInfo.DBName];
        Database destDb = new Database(server, destDbInfo.DBName);
        if (isFromConsole) Console.WriteLine("Creating Database...");
        destDb.Create();
        if (isFromConsole) Console.WriteLine("Database successfully created...");
        
        // Create Transfer object
        Transfer transfer = new Transfer(srcDb);
        transfer.Options.WithDependencies = true;
        transfer.Options.ContinueScriptingOnError = true;
        
        // MODIFIED: Turn off full database object copy
        transfer.CopyAllObjects = false;
        transfer.CopyAllSchemas = true; // Keep if your target tables use non-default schemas
        transfer.CopyAllUserDefinedDataTypes = true; // Keep if tables rely on custom data types
        transfer.CopyAllTables = false; // Critical: Disable full table copy
        transfer.CopyData = true; // We still want data for our specific tables
        transfer.CopyAllStoredProcedures = false; // Disable if you don't need all stored procs
        
        transfer.Options.DriAll = true; // Keep to copy constraints/indexes for target tables
        transfer.DestinationServer = server.Name;
        transfer.DestinationDatabase = destDb.Name;
        transfer.DestinationLoginSecure = false;
        transfer.DestinationLogin = destDbInfo.UserID;
        transfer.DestinationPassword = destDbInfo.Password;
        
        // ADDED: Define your specific tables here (include schema if needed)
        List<string> targetTables = new List<string> {
            "Customers", 
            "Orders", 
            "Sales.OrderDetails" // Example with non-default schema
        };
        
        // ADDED: Add each target table to the transfer collection
        foreach (var tableName in targetTables)
        {
            // Split schema and table name if specified
            string schemaName = "dbo";
            string pureTableName = tableName;
            if (tableName.Contains('.'))
            {
                var parts = tableName.Split('.');
                schemaName = parts[0];
                pureTableName = parts[1];
            }
            
            Table targetTable = srcDb.Tables[pureTableName, schemaName];
            if (targetTable != null)
            {
                transfer.ObjectsToTransfer.Add(targetTable);
                if (isFromConsole) Console.WriteLine($"Added table {tableName} to transfer list");
            }
            else
            {
                if (isFromConsole) Console.WriteLine($"Warning: Table {tableName} not found in source database");
            }
        }
        
        if (isFromConsole) Console.WriteLine("Transferring data for specified tables...");
        transfer.TransferData();
        if (isFromConsole) Console.WriteLine("Transfer completed...");
    }
} catch (Exception ex) {
    throw ex;
}

Important Notes #

• Schema Handling: If your tables aren't in the default dbo schema, make sure to specify the full SchemaName.TableName in your target list. The code above handles both default and custom schema cases.
• Dependencies: The WithDependencies = true setting will include objects that the target tables depend on (like lookup tables linked via foreign keys). If you don't want these dependencies transferred, set this value to false.
• Validation: The code includes a check to verify each target table exists in the source database, which helps avoid unexpected runtime errors.

内容的提问来源于stack exchange，提问作者Rashid