How to move table data in one server to another server

[Dhanasekaran B]

Hi,
I need to move one table in one server(Live) to another server(Development) through Visual basic coding. Please send soultion for this

[dorinbogdan]

Supposing that the table exists on the destination database:

Private Function ExportTable(tbl as String)


End Function

[dorinbogdan]

I'm sorry for the previous incomplete message.
I'll write a sample code in few moments...

[dorinbogdan]

The solution I suggest is to use 2 ADODB.Recordset objects, and 2 ADODB.Connection objects (it requires to add a reference to Microsoft ActiveX Data Objects in Project/References)
You just have to update the connection strings strConn1 and strConn2.
(I couldn't check the code, since I don't have VB6 installed).
Don't forget to create the destination table with same structure as source table.

Private Function ExportTable(strTable As String) 
Dim i As Integer
Dim rs1 As Object 'ADODB.Recordset
Dim rs2 As Object 'ADODB.Recordset
Dim cn1 As Object 'ADODB.Connection
Dim cn2 As Object 'ADODB.Connection
Dim strConn1 As String
Dim strConn2 As String
On Error Goto EH
    ExportTable = False
    strConn1 = "Driver={SQL Server};Server=Live;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    strConn2 = "Driver={SQL Server};Server=Development;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
    set cn1 = CreateObject("ADODB.Connection")
    set cn2 = CreateObject("ADODB.Connection")
    set rs1 = CreateObject("ADODB.Recordset")
    set rs2 = CreateObject("ADODB.Recordset")
    cn1.Open  strConn1
    cn2.Open  strConn2
 
    rs1.Open "Select * From " & strTable , cn1, adOpenStatic, adLockReadOnly
    rs2.Open "Select top 0 * From " & strTable , cn2, adOpenDynamic, adLockPessimistic
    rs1.MoveFirst
    If Not (rs1.EOF and rs1.BOF) Then  
        While not rs1.EOF
            rs2.AddNew
            For i = 0 to rs1.Fields.Count-1
                rs2.Fields(i) = rs1.Fields(i) 
            Next
            rs2.Update
            rs1.MoveNext 
        Wend
    End If
    rs1.Close
    rs2.Close
 
    ExportTable = True
 
EH:
    MsgBox Err.Number & ": " & Err.Description 
End Function