CODEWITHSUNDEEP
sqlsql-serverdatabasevb.netsmo
iamlawrencev
iamlawrencev

Reputation: 125

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

Upvotes: 2

Views: 1180

Answers (1)

Indian
Indian

Reputation: 527

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.

Upvotes: 2

Related Questions