Loop Through Reader, Select Data, Insert into table.

       Dim datacommand As New SqlCommand
        Dim reader As SqlDataReader

        datacommand = New SqlCommand
        datacommand.Connection = New SqlConnection("Coonection string here")
        datacommand.Connection.Open()
        datacommand.CommandText = "select ID, col1,col2 from table1"
        reader = datacommand.ExecuteReader()

        If reader.Read() Then
          Dim myconnection As SqlConnection
      Dim mycommand As SqlCommand
myconnection = New SqlConnection("Coonection string here")
      myconnection.Open()
         mycommand = New SqlCommand("insert into table2(ID) values (" & reader("id") & ")", myconnection)
                     mycommand.ExecuteNonQuery()
      myconnection.Close()
      

        End If

        reader.Close()
        datacommand.Connection.Close()

refer

http://www.developerbarn.com/net-development/606-sqldatareader-read-next-record.html
http://www.dreamincode.net/forums/topic/66695-insert-data-into-sql-server-in-vbnet-by-user-input/
http://msdn.microsoft.com/en-us/library/aa720188(v=vs.71).aspx

Dim objDR As SqlClient.SqlDataReader
Dim objCommand As SqlClient.SqlCommand
Dim ConnectionString As String = "YOUR CONNECTION STRING HERE"
Dim objConnection As SqlClient.SqlConnection
Dim ssql As String
       
objConnection = New SqlClient.SqlConnection(ConnectionString)
ssql = "SELECT * FROM someTable"
       
If objConnection.State <> ConnectionState.Open Then
 objConnection.Open()
End If
objCommand = New SqlClient.SqlCommand(ssql, objConnection)
objDR = objCommand.ExecuteReade(CommandBehavior.CloseConnection)
objCommand = Nothing
      
If objDR.HasRows Then
      While objDR.Read()
            ' do your code here
            ' the following gives access
            ' to the table's field:
            ' objDR.Item("someField")
        ' Insert statement goes here
      End While
End If
objDR.Close()
objDR = Nothing

I think the easy method is a unique sentence that inserts data on Table2 Like:

   Insert into Table2(id) Select distinct id from Table1

If Table2 has rows then:

   Insert into Table2(id) ( Select id from Table1 minus Select Id from Table2)
   

Sorry i do´nt note that there are two DataBases.
Note that you can use DabaBase link.

You says "datareader loop through a table and select the distinct employee ID"
But your sentence do´nt use distinct clause, then you can need a Dictionary or HashTable to get Distinct Id

  Dim D as new Dictionary (of object, boolean)  , v as object      
       ....
 on Reader Loop:
     if D.TryGetValue( Id , v) = false then D.add( Id, true)

After Reader Loop build a loop to enumerate dictionary and insert on Table 2.

     for each Id in D.Keys
        Insert into Table2(v) ...
     next          
     

Thank you for the quick response. Ill work with the code and get right back to you.

After working with my API for a while, I see that each call that I make, I will only bring back a single value each time.  So...

I was wondering if you could tell me how I can loop through table1 and supply an employeeID for each API call and populate table1 with the returned value until all rows in table one has been processed.

Portion of my API Code is below:

I want to use a method "GetEmployeeID" to keep feeding the API until all rows in table 1 is complete.

I also want to populate table1 with the EmpMgrName where employeeID = employeeID each time.

 

Dim strQry As String = "SELECT GetAPIEmpMgrNameCall"
        strQry += "FROM apTable WHERE IDNo = " & GetEmployeeID()
        Dim intErr As Integer = API.QueryGT(hQry, strQry)
        If (intErr <> 0) Then
            Console.WriteLine("Error Obtaining Employee Manager Name")
            Return
        End If

        EmpMgrName = API.StrValue(hQry, 0)

Select allOpen in new window

Can you show us your complete code?

Here's an encripted version of what I'm working with.

I will have about 5 API Calls.  Each call has to update a table... final step for a database email that I have set up.

Module EData
    
    Public Sub MgrName(ByVal IDNo As String)
        Dim MgrName As String

        Dim sAI As MyAPIStuff.MyAPIStuff = New MyAPIStuff.MyAPIStuff()
        sAI.Commence()

        Dim sAIODBCSource As String = System.Configuration.ConfigurationManager.AppSettings("Connect").ToString()
        Dim uNm As String = System.Configuration.ConfigurationManager.AppSettings("TheAccount").ToString()
        Dim pwrd As String = System.Configuration.ConfigurationManager.AppSettings("ThePassword").ToString()
        Dim intConn As Integer = sAI.AmOpenConnection(sAIODBCSource & Chr(0), uNm & Chr(0), _
                                                     pwrd & Chr(0))

        Dim errMsg As String = sAI.LtErrMg(intConn)
        If (errMsg.Length > 0) Then
            Console.WriteLine("API Connection = " & intConn & _
                     "  Error Message: " & sAI.LtErrMg(intConn))
        End If
        If (intConn = 0) Then Return

        sAI.UptLogSlot(intConn)
        Dim lLoginId As Long = sAI.Log(intConn)

        Dim hQry As Integer = sAI.QryCrte(intConn)
        If (hQry = 0) Then
            Console.WriteLine("hQry = 0")
            Return
        End If

        Dim strQry As String = "SELECT cf_Get_Employee_Manager_Name "
        strQry += "My API Query = " & GetEmployeeID()
        Dim intErr As Integer = sAI.QryGt(hQry, strQry)
        If (intErr <> 0) Then
            Console.WriteLine("Error Obtaining Employee Manager Name")
            Return
        End If

        MgrName = sAI.GtFdStrVal(hQry, 0)



        sAI.Release(hQry)
        intConn = sAI.CloseConn(intConn)
        sAI = Nothing
    End Sub


    Public Function GetEmployeeID()
        Dim EmployeeID As String = " "
        Try

            Dim conn As SqlConnection = New SqlConnection(AppConfig.MYConnectionString)
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT EmployeeID from Table"
            conn.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            If (rd.Read() = True) Then
                GetEmployeeID = rd("EmployeeID")
            End If
            rd.Close()
            conn.Close()


        Catch ex As Exception
            Console.WriteLine("Error Retrieving Employee Number")
        End Try

        Return EmployeeID

    End Function

End Module

Select allOpen in new window

That code is to retrieve manager name right? I thought your question was about "Loop Through Reader, Select Data, Insert into table."!

The "Public Function GetEmployeeID" has to loop through the table and for each employee number, the employee manager is pulled using the API.  The manager has to be saved into the table at the same row that the employee number was pulled.

Hope that explains what I'm trying to achieve.  
Sorry for the mess.

I'm currently working through and trying to use the Entity Framework.  I think it can handle the looping through a list of employeeID and efficiently save that string to the database.

This looks beautiful! Thanks.  I'll try it and get back to you.

Sorry for the delay in responding.  I was having trouble with the API dll.
After evaluating the code, I see that I was not clear in my explanation.  

The Public Function MgrName Has to first read the Manager ID from the table Normal SQL Connection.

This then supplies the correct parameter that will be passed through the API to get the Manager Name

Then the name is added to the same table at the same row.   Since the employee number is distinct, I could say something like (ByRef IDNo As String, ByVal FuncManID As String)?

You have a table of employees with a column containing the managerID and now you want to add another column with manager name but populate it automatically using managerID?

CodeCruiser,  The API function will allow me to pass in the employeeID I thought that I had to pass in managerID.

I am working on populating one table from multiple sources and systems.  I was able to work with Entity framework and loop through a list of EmployeeID  from that table.  Now I am trying to figure out how to save the EmployeeManagerName to the table where EmployeeID = the EmployeeID that I pass in.  Please see the following:

I created the following two methods in a repository class.
Public Function GetEmployeeID() As System.Linq.IQueryable(Of Table1)
        Dim dataContext = New MyEntities()
        Dim EmpManName As String = " "
        Dim EmployeeIdList = (From n In dataContext.Table1 Select n.EmployeeID).ToList
        For Each n In EmployeeIdList
            GetManID(n.ToString, EmpManName)
        Next n
        Return EmployeeIdList

    End Function

    Public Function GetManID(ByVal id, ByVal EmpManName)
        Dim dataContext = New MyEntities()
        Dim ManName = From n In dataContext.Table1
                    Where n.EmployeeID = id
                    Select n.FunctionalMgrName.FirstOrDefault

        Return ManName

    End Function


My program has API methods that retrieve the the manager name from another system as I pass in the employeeID.

How do I save the string to Table1 

Here's the end of the API method that I am working on:

 Return FuncMgrName = MyAPI.APIStrValue(hQry, 0)

        Dim repository As New CasualDataRepository
        Dim SavMan = repository.GetManID(EmployeeID, FuncMgrName)
        repository.SaveChanges()

Select allOpen in new window

>I am working on populating one table from multiple sources and systems.
Does it have to be done in code? Can be done in SQL. You can join multiple tables to get your desired resultset in SQL.

I have to work with this in code because of the secured system.
I almost got it.  

I am going to take out the loop from here and call this function in code... to do this, I have to change this from list and just return the employeeID because I am getting an invalid cast:

Public Function GetEmployeeID() As System.Linq.IQueryable(Of Table1)
        Dim dataContext = New MyEntities()
        Dim EmpManName As String = " "
        Dim EmployeeIdList = (From n In dataContext.Table1 Select n.EmployeeID).ToList
       
        Return EmployeeIdList

    End Function


Here's a part of what I have to process in code:
Dim repository As New MyDataRepository
        repository.GetEmployeeID()
        For Each n In repository.GetEmployeeID

            Dim strQry As String = "SELECT HRMangerName"
            strQry += "FROM amEmplDept WHERE IDNo = " & n.EmployeeID
            Dim intErr As Integer =MyAPI.QueryGet(hQry, strQry)
            If (intErr <> 0) Then
                Console.WriteLine("Error Obtaining Employee HR Manager Name")
                Return
            Else
                HRMgrName = MyAPI.FieldStrValue(hQry, 0)
                repository.GetTalentManID(n.EmployeeID, HRMgrName)
                repository.SaveChanges()

            End If

It took a while to get this solution to work correctly due to version and compatibility issues between the API and the version of .net framework it would work with.  I ended up using a version of this solution after I rewrote it using Visual Studio 2005.  

Thank you very much for your assistance.