Append Data

Discussion in 'ASP .Net' started by Reggie, Jun 22, 2005.

  1. Reggie

    Reggie Guest

    Hi and TIA. I have an OleDB connection to an Access db which I connect to
    and place data from a table into a recordset. What I want to do is copy
    this recordset to an SQL table. I have no problem connecting to the SQL
    table but can't figure out how to pump the data from the recordset into the
    table. Was wondering how to do it and what would be the fastest procedure.
    The recordset usually consist of 20 to 100K records. Thanks very much for
    your time!

    --

    Reggie
    Reggie, Jun 22, 2005
    #1
    1. Advertising

  2. Hi Reggie,

    The simplest way is to loop thru recordset and create Insert sql command to
    insert data into the DB. However, it’s slow especially for large records of
    data. Instead, you can create middle step text data file (using StringBuilder
    can improve performance), then apply bulk insert.

    BTW, why do you use Recordset rather than DataTable?

    HTH

    Elton Wang


    "Reggie" wrote:

    > Hi and TIA. I have an OleDB connection to an Access db which I connect to
    > and place data from a table into a recordset. What I want to do is copy
    > this recordset to an SQL table. I have no problem connecting to the SQL
    > table but can't figure out how to pump the data from the recordset into the
    > table. Was wondering how to do it and what would be the fastest procedure.
    > The recordset usually consist of 20 to 100K records. Thanks very much for
    > your time!
    >
    > --
    >
    > Reggie
    >
    >
    >
    =?Utf-8?B?RWx0b24gVw==?=, Jun 22, 2005
    #2
    1. Advertising

  3. Reggie

    Reggie Guest

    Also, If I could simply loop through my dataset, grab each record and run
    an Update query that would be fine. Can't figure out how to setup the loop
    and grab the data. Any advice or pointers, or examples are appreciated.
    Thanks again for your time!

    --

    Reggie
    "Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
    news:...
    > Hi and TIA. I have an OleDB connection to an Access db which I connect to
    > and place data from a table into a recordset. What I want to do is copy
    > this recordset to an SQL table. I have no problem connecting to the SQL
    > table but can't figure out how to pump the data from the recordset into
    > the table. Was wondering how to do it and what would be the fastest
    > procedure. The recordset usually consist of 20 to 100K records. Thanks
    > very much for your time!
    >
    > --
    >
    > Reggie
    >
    Reggie, Jun 22, 2005
    #3
  4. Reggie

    Reggie Guest

    Elton, I say recordset (old VB habit), but I'm actually using a dataset
    filled by a MS ASccess DataTable. Would you happen to have any example of
    this process cause I can't find anything on looping through the dataset. In
    vb I could use do until rst.eof but doesn't seem to work on the dataset.
    I'll look into your second suggestion. I came across this in my research,
    but thought the looping would be faster. Goes to show you what I know.
    Anyway thanks much for your time!

    --

    Reggie
    "Elton W" <> wrote in message
    news:...
    > Hi Reggie,
    >
    > The simplest way is to loop thru recordset and create Insert sql command
    > to
    > insert data into the DB. However, it's slow especially for large records
    > of
    > data. Instead, you can create middle step text data file (using
    > StringBuilder
    > can improve performance), then apply bulk insert.
    >
    > BTW, why do you use Recordset rather than DataTable?
    >
    > HTH
    >
    > Elton Wang
    >
    >
    > "Reggie" wrote:
    >
    >> Hi and TIA. I have an OleDB connection to an Access db which I connect
    >> to
    >> and place data from a table into a recordset. What I want to do is copy
    >> this recordset to an SQL table. I have no problem connecting to the SQL
    >> table but can't figure out how to pump the data from the recordset into
    >> the
    >> table. Was wondering how to do it and what would be the fastest
    >> procedure.
    >> The recordset usually consist of 20 to 100K records. Thanks very much
    >> for
    >> your time!
    >>
    >> --
    >>
    >> Reggie
    >>
    >>
    >>
    Reggie, Jun 22, 2005
    #4
  5. Actually, data is in DataTable. You should loop thru datatable:

    Dim name As String
    ' Suppose the first table. Or you can use dataset.Tables("table_name")
    For Each row As DataRow In dataset.Tables(0)
    name = row("Name").ToString
    ' orther field

    Next


    Elton

    "Reggie" wrote:

    > Elton, I say recordset (old VB habit), but I'm actually using a dataset
    > filled by a MS ASccess DataTable. Would you happen to have any example of
    > this process cause I can't find anything on looping through the dataset. In
    > vb I could use do until rst.eof but doesn't seem to work on the dataset.
    > I'll look into your second suggestion. I came across this in my research,
    > but thought the looping would be faster. Goes to show you what I know.
    > Anyway thanks much for your time!
    >
    > --
    >
    > Reggie
    > "Elton W" <> wrote in message
    > news:...
    > > Hi Reggie,
    > >
    > > The simplest way is to loop thru recordset and create Insert sql command
    > > to
    > > insert data into the DB. However, it's slow especially for large records
    > > of
    > > data. Instead, you can create middle step text data file (using
    > > StringBuilder
    > > can improve performance), then apply bulk insert.
    > >
    > > BTW, why do you use Recordset rather than DataTable?
    > >
    > > HTH
    > >
    > > Elton Wang
    > >
    > >
    > > "Reggie" wrote:
    > >
    > >> Hi and TIA. I have an OleDB connection to an Access db which I connect
    > >> to
    > >> and place data from a table into a recordset. What I want to do is copy
    > >> this recordset to an SQL table. I have no problem connecting to the SQL
    > >> table but can't figure out how to pump the data from the recordset into
    > >> the
    > >> table. Was wondering how to do it and what would be the fastest
    > >> procedure.
    > >> The recordset usually consist of 20 to 100K records. Thanks very much
    > >> for
    > >> your time!
    > >>
    > >> --
    > >>
    > >> Reggie
    > >>
    > >>
    > >>

    >
    >
    >
    =?Utf-8?B?RWx0b24gVw==?=, Jun 22, 2005
    #5
  6. Some thing wrong in previous post

    Code should be
    For Each row As DataRow In daset.Tables(0).Rows
    name = row("Name").ToString
    ' orther field
    Next

    Elton


    "Reggie" wrote:

    > Elton, I say recordset (old VB habit), but I'm actually using a dataset
    > filled by a MS ASccess DataTable. Would you happen to have any example of
    > this process cause I can't find anything on looping through the dataset. In
    > vb I could use do until rst.eof but doesn't seem to work on the dataset.
    > I'll look into your second suggestion. I came across this in my research,
    > but thought the looping would be faster. Goes to show you what I know.
    > Anyway thanks much for your time!
    >
    > --
    >
    > Reggie
    > "Elton W" <> wrote in message
    > news:...
    > > Hi Reggie,
    > >
    > > The simplest way is to loop thru recordset and create Insert sql command
    > > to
    > > insert data into the DB. However, it's slow especially for large records
    > > of
    > > data. Instead, you can create middle step text data file (using
    > > StringBuilder
    > > can improve performance), then apply bulk insert.
    > >
    > > BTW, why do you use Recordset rather than DataTable?
    > >
    > > HTH
    > >
    > > Elton Wang
    > >
    > >
    > > "Reggie" wrote:
    > >
    > >> Hi and TIA. I have an OleDB connection to an Access db which I connect
    > >> to
    > >> and place data from a table into a recordset. What I want to do is copy
    > >> this recordset to an SQL table. I have no problem connecting to the SQL
    > >> table but can't figure out how to pump the data from the recordset into
    > >> the
    > >> table. Was wondering how to do it and what would be the fastest
    > >> procedure.
    > >> The recordset usually consist of 20 to 100K records. Thanks very much
    > >> for
    > >> your time!
    > >>
    > >> --
    > >>
    > >> Reggie
    > >>
    > >>
    > >>

    >
    >
    >
    =?Utf-8?B?RWx0b24gVw==?=, Jun 22, 2005
    #6
  7. Reggie

    Reggie Guest

    Elton thanks for setting me straight and for the code snippet. Very much
    appreciated.

    --

    Reggie
    "Elton W" <> wrote in message
    news:D...
    > Some thing wrong in previous post
    >
    > Code should be
    > For Each row As DataRow In daset.Tables(0).Rows
    > name = row("Name").ToString
    > ' orther field
    > Next
    >
    > Elton
    >
    >
    > "Reggie" wrote:
    >
    >> Elton, I say recordset (old VB habit), but I'm actually using a dataset
    >> filled by a MS ASccess DataTable. Would you happen to have any example
    >> of
    >> this process cause I can't find anything on looping through the dataset.
    >> In
    >> vb I could use do until rst.eof but doesn't seem to work on the dataset.
    >> I'll look into your second suggestion. I came across this in my
    >> research,
    >> but thought the looping would be faster. Goes to show you what I know.
    >> Anyway thanks much for your time!
    >>
    >> --
    >>
    >> Reggie
    >> "Elton W" <> wrote in message
    >> news:...
    >> > Hi Reggie,
    >> >
    >> > The simplest way is to loop thru recordset and create Insert sql
    >> > command
    >> > to
    >> > insert data into the DB. However, it's slow especially for large
    >> > records
    >> > of
    >> > data. Instead, you can create middle step text data file (using
    >> > StringBuilder
    >> > can improve performance), then apply bulk insert.
    >> >
    >> > BTW, why do you use Recordset rather than DataTable?
    >> >
    >> > HTH
    >> >
    >> > Elton Wang
    >> >
    >> >
    >> > "Reggie" wrote:
    >> >
    >> >> Hi and TIA. I have an OleDB connection to an Access db which I
    >> >> connect
    >> >> to
    >> >> and place data from a table into a recordset. What I want to do is
    >> >> copy
    >> >> this recordset to an SQL table. I have no problem connecting to the
    >> >> SQL
    >> >> table but can't figure out how to pump the data from the recordset
    >> >> into
    >> >> the
    >> >> table. Was wondering how to do it and what would be the fastest
    >> >> procedure.
    >> >> The recordset usually consist of 20 to 100K records. Thanks very much
    >> >> for
    >> >> your time!
    >> >>
    >> >> --
    >> >>
    >> >> Reggie
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
    Reggie, Jun 23, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tina I
    Replies:
    9
    Views:
    290
    Tina I
    Apr 15, 2007
  2. HYRY
    Replies:
    10
    Views:
    574
    Bruno Desthuilliers
    Sep 26, 2007
  3. mosfet

    append data to a vector

    mosfet, Sep 28, 2007, in forum: C++
    Replies:
    9
    Views:
    501
    Howard
    Nov 15, 2007
  4. BerlinBrown
    Replies:
    4
    Views:
    427
    Neil Cerutti
    Jan 15, 2008
  5. Replies:
    1
    Views:
    323
    Dave Miller
    May 9, 2008
Loading...

Share This Page