7.4 Transfer Tables Between SQL Server Databases
Users sometimes need to transfer (copy) tables between SQL Server databases. This
How-To shows how to allow the user to choose multiple tables and copy them from one
database to another as well as tables from two different databases on two different SQL
servers.
One of the tasks your clients have you perform for them using the Enterprise Manager is
to transfer objects, such as tables, between SQL Server databases. How do you create a
dialog box that would allow the user to transfer databases between two SQL Server
databases?
Technique
Unlike the earlier How-Tos in this chapter, you will be using the SQL-DTS object model
in addition to SQL-DMO. You can see the objects, properties, and methods that will be
used from SQL-DTS in Table 7.7.
Table 7.7. SQL-DTS Objects That Are Used to Perform the Transfer of Tables from
One SQL Server Database to Another
Object Property/Method
Package Steps.New
Tasks.New
Steps.Add
Tasks.Add
Execute
Step TaskName
Name
Task CustomTask
CustomTask Name
SourceServer
SourceUseTrustedConnection
SourceDatabase
DestinationServer
DestinationUseTrustedConnection DestinationDatabase
CopyAllObjects
IncludeDependencies
IncludeLogins
IncludeUsers
DropDestinationObjectsFirst
CopySchema
CopyData
AddObjectForTransfer
Using the items just listed, you will create a form with options to transfer tables between
two SQL databases.
Steps
Open and run the VB.NET -Chapter 7 solution. From the main Windows form, click on
the command button with the caption How-To 7.4. You will then see a form allowing you
to pick SQL Servers on the network to transfer from and to. After you have chosen these,
you can then select which databases you want to transfer from and to. After choosing
from the database to transfer from, you are then presented with a list of tables to transfer
from. You can then highlight multiple tables, as shown in Figure 7.9.
Figure 7.9. Transferring tables between SQL Server databases.
Tip
One of the options included as a property of the CustomTask object is
IncludeDependencies. This option specifies whether to have DTS transfer
related tables as well as the selected table(s). This could be put as an
option on the form as well. For this example, I have it set to True.
1. Create a Windows Form. Then place the controls shown in Figure 7.9, with the
following properties set as in Table 7.8.
Table 7.8. Label, ListBox, and Command Button Controls Property Settings
Object Property Setting
Label Name Label1
Text From SQL Servers
ListBox Name lstFromSQLServer
Label Name Label2
Text To SQL Servers
ListBox Name lstToSQLServer
Label Name Label3
Text Transfer from Database
ListBox Name lstFromDB
Label Name Label4
Text Transfer to Database
ListBox Name lstToDB
Label Name Label5
Text Table(s) to Transfer
ListBox Name lstTables
SelectionMode MultiSimple
Command Button Name btnTransfer
Text &Perform Transfer
2. On the form, add the code in Listing 7.18 to the Load event. This will look
familiar from How-To 7.1. For an examination of the LoadSQLServers routine,
check out step 4 in that How-To. Different from the other How-Tos in this chapter
thus far, however, is the fact that the LoadSQLServers routine is called twice:
once for the lstFromSQLServer, and a second time for the lstToSQLServer.
Listing 7.18 frmHowTo7_4.vb: Calling the Routine That Loads Available
SQL Servers into a List Box
Private Sub frmHowTo7_4_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
LoadSQLServers(Me.lstFromSQLServer)
LoadSQLServers(Me.lstToSQLServer)
End Sub
3. On the lstFromSQLServer and lstToSQLServer list boxes, add the code in Listing
7.19 to the SelectedIndexChanged event of each, as appropriate. These routines
call GetSQLDatabases, described in step 6 of How-To 7.1.
Listing 7.19 frmHowTo7_4.vb: Populating the lstDatabases and
lstBackupDevices List Boxes
Private Sub lstFromSQLServer_SelectedIndexChanged(ByVal sender As
System.Object,
ByVal e As System.EventArgs) Handles
lstFromSQLServer.SelectedIndexChanged
GetSQLDatabases(Me.lstFromSQLServer.SelectedItem, Me.lstFromDB)
End Sub
Private Sub lstToSQLServer_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) _
Handles lstToSQLServer.SelectedIndexChanged
GetSQLDatabases(Me.lstToSQLServer.SelectedItem, Me.lstToDB)
End Sub
4. On the lstFromTables list box, add the code in Listing 7.20 to the
SelectedIndexChanged event. This routine starts off by logging onto the server
that is selected in the lstFromSQLServer list box, and then creates a reference to
the database that is selected in the lstFromDB list box. After clearing the lstTables
list box, the routine iterates through each of the tables in the database and adds the
names of those that are user tables to the lstTables items.
Listing 7.20 frmHowTo7_4.vb: Populating the lstDatabases and
lstBackupDevices List Boxes
Private Sub lstFromDB_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstFromDB.SelectedIndexChanged
'-- Create the connection and specify the stored procedure to use.
Dim odb As SQLDMO.Database
Dim otbl As SQLDMO.Table
Dim oapp As New SQLDMO.Application()
Dim osvr As New SQLDMO.SQLServer()
Try
osvr.LoginSecure = True