Search code examples
sqlsql-serverdatabasevb.netsmo

How can I transfer a single table to another database in MSSQL server using VB.NET's SMO class?


I can already create a database but when I use the code below, it transfers all the tables without any records. Additionally how would I code that only a single specific table will be transferred?

Dim conn As New ServerConnection("NPSS-OJT", "sa", "rms")
        Dim srv As New Server(conn)
        Dim db As Database = srv.Databases("bos_primary_db")
        Dim dbCopy As Database = New Database(srv, Main.dbName)

        Dim xfr As Transfer
        xfr = New Transfer(db)
        xfr.CopyAllTables = True
        xfr.Options.WithDependencies = True
        xfr.Options.ContinueScriptingOnError = True
        xfr.DestinationDatabase = dbCopy.Name
        xfr.DestinationServer = srv.Name
        xfr.DestinationLoginSecure = True
        xfr.CopySchema = True
        xfr.CopyData = True

        xfr.TransferData()

Updated Code:

Shared Sub transferTables()
        Dim conn As New ServerConnection("NPSS-OJT", "sa", "rms")
        Dim srv As New Server(conn)
        Dim db As Database = srv.Databases("bos_primary_db")
        Dim dbCopy As Database = New Database(srv, Main.dbName)

        Dim xfr As Transfer
        xfr = New Transfer(db)
        xfr.CopyAllTables = False
        xfr.Options.WithDependencies = False
        xfr.Options.ContinueScriptingOnError = True
        xfr.DestinationDatabase = dbCopy.Name
        xfr.DestinationServer = srv.Name
        xfr.DestinationLoginSecure = True
        xfr.CopySchema = True
        xfr.CopyData = True
        xfr.Options.DriAll = False
        xfr.Options.DriDefaults = True
        xfr.Options.DriIndexes = True
        xfr.Options.DriPrimaryKey = True
        xfr.Options.DriUniqueKeys = True
        xfr.Options.DriForeignKeys = False
        xfr.ObjectList.Add("Accounting")
        xfr.TransferData()
    End Sub

Solution

  • You should set

    xfr.CopyAllTables = False
    xfr.Options.WithDependencies = False
    

    also you should transfer keys

    xfr.Options.DriAll = False 
    xfr.Options.DriDefaults = True
    xfr.Options.DriIndexes = True
    xfr.Options.DriPrimaryKey = True
    xfr.Options.DriUniqueKeys = True
    xfr.Options.DriForeignKeys = False
    

    and then

    EDIT (I changed adding of the table in the list of transfered objects)

    xfr.ObjectList.Add(db.Tables("table_name"))
    xfr.TransferData()
    

    To transfer specified object between databases you need to add this object to ObjectList but not just the object's name.