Copy Record(s) from 1 Database to Another Database with the same Tables

[crmfghtr]

Hi Everyone, I hope I'm posting this in the right place. I'm using VistaDb version 6.2 I have a Main Database with several tables Like Clients, Inventory, etc. I have a Backup Database with the same table(s) and structure as the Main database. I'm trying to copy 1 or more records from the Backup Database, like the Inventory Table to the Main Database Inventory Table. The code below is inserting records from the Main database inventory table, and not from the backup database. Do I need to do this by First selecting the data and placing it in a tmp table then insert the records from the tmp table? Any help would be appreciated. Here is the code I have:

Private Sub TransferInventory()

    Dim MyFile As String = TreeListTransfer.FocusedNode(0).ToString
    Dim VdbConn1 As String = "Data Source=" & Application.StartupPath & "\\Backup\\" & MyFile & ".vdb6"
    Try
        'Count the files in the Listbox
        For i As Integer = 0 To LstFiles.Items.Count - 1
            LstFiles.SelectedIndex = i
            FileId = CInt(LstFiles.Text)

            'Add the Contents to the Database
            Using conn As New VistaDBConnection(VdbConn)
                conn.Open()
                StrSql = "INSERT INTO Inventory(ClientId, Category, Product, PartNo, PurchaseDate, Unit, UnitPrice, InStock, OnOrder, Photo) 
                      SELECT ClientId, Category, Product, PartNo, PurchaseDate, Unit, UnitPrice, InStock, OnOrder, Photo
                      FROM dbo.Inventory
                      WHERE (InventoryId = @InventoryId)"

                Using cmd As New VistaDBCommand(StrSql, conn)
                    With cmd.Parameters
                        .AddWithValue("@InventoryId", FileId)
                    End With
                    'Execute the Statement

                    cmd.ExecuteNonQuery()
                    'Close the Connection
                    conn.Close()
                End Using
            End Using

        Next
        MessageBox.Show("Transfer successfully completed on the 'Inventory' table", "Transfer Inventory Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Transfer Inventory Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    Finally

    End Try

End Sub

Thanks in Advance.

[Victor Nijegorodov]

crmfghtr wrote:

he code below is inserting records from the Main database inventory table, and not from the backup database

It is because you don't mention in your SQL query from/to what database you select/insert the data. See [sql - How to insert table values from one database to another database? - Stack Overflow](https://stackoverflow.com/questions/3502269/how-to-insert-table-values-from-one-database-to-another-database)