none
Insert into..select from... tables with different connection strings. RRS feed

  • Question

  • I have two tables from different databases.  Rows from table 1 need to be inserted into table 2.  How do I code this in vb?

    I'm rewriting Access queries in vb.net.  This will involve two connection strings and commands.  But I cannot figure out how to format the sql.  Thanks for your help.

    INSERT INTO table2 ( <columnA>, <columnB> )
    SELECT table1.columnA, table1.columnB
    FROM table1


    Using air code, it would be something like this (I think):

            Dim strSQL As String = String.Empty
            Dim conn1 As New connForTable1
            Dim cmd1 As New SqlCommand
            Dim conn2 As New connForTable2
            Dim cmd2 As New SqlCommand
    
            strSQL = _
                    "INSERT INTO Table2" & _
                    "(columnA, columnB) " & _
    		"SELECT FROM Table1 " & _
                    "columnA, columnB " & _
    		"WHERE <condition>"
            Try
                conn1.table1conn.Open()
                cmd1.Connection = conn1.table1conn
    
                conn2.table2conn.Open()
                cmd2.Connection = conn2.table2conn
    
    
                cmd?.CommandText = strSQL
    
                cmd?.ExecuteNonQuery()

    But I'm not sure how to finish it because of the different connection strings and commands.

    Wednesday, November 28, 2018 4:12 PM

Answers

  • For the last part, maybe create an SqlCommand based on the second connection, assign it to da.UpdateCommand, perhaps mark all rows as Modified using DataRow.SetModified, then execute da.Update(dt).

    See the documentation for UpdateCommand about setting the parameters for this SqlCommand.



    • Edited by Viorel_MVP Wednesday, November 28, 2018 6:56 PM
    • Marked as answer by em_rdh Thursday, November 29, 2018 9:41 PM
    Wednesday, November 28, 2018 6:34 PM

All replies

  • I believe I've figured it out.  Read all rows from table 1 into a datatable.  Then loop thru the datatable and insert datarow into table2.  Like so:

     
                conn1.table1conn.Open()
                cmd1.Connection = conn1.table1conn
    
                strSQL = "SELECT " & _
                    "columnA" & _
                    ", columnB" & _
                	"FROM Table1 " & _
                    "WHERE Table1.columnA = 'I')"
    
                Dim da As New SqlDataAdapter(strSQL, conn1.table1conn)
                Dim dt As New DataTable
                da.Fill(dt)
    
                For Each row As DataRow In dt.Rows
                    strSQL = _
                            "INSERT INTO Table2 (" & _
                            "columnA" & _
                            ", columnB)" & _
                            "VALUES (" & _
                            "@columnA" & _
                            ", @columnB" 
    		use conn2 and cmd2 to execute the insert.  etc, etc...

    But if there's a better way, please advise.

    Wednesday, November 28, 2018 5:01 PM
  • For the last part, maybe create an SqlCommand based on the second connection, assign it to da.UpdateCommand, perhaps mark all rows as Modified using DataRow.SetModified, then execute da.Update(dt).

    See the documentation for UpdateCommand about setting the parameters for this SqlCommand.



    • Edited by Viorel_MVP Wednesday, November 28, 2018 6:56 PM
    • Marked as answer by em_rdh Thursday, November 29, 2018 9:41 PM
    Wednesday, November 28, 2018 6:34 PM
  • "INSERT INTO tblSysInfo SELECT * FROM [MS Access;DATABASE=" & OpenLastDatabase & "].[tblSysInfo]"

    Best Regard

    Xan To

    • Proposed as answer by Xan To Wednesday, November 28, 2018 6:56 PM
    • Edited by Xan To Wednesday, November 28, 2018 6:58 PM
    Wednesday, November 28, 2018 6:56 PM