Mass copy of data in recordset to another recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Mass copy of data in recordset to another recordset

  1. #1
    Alpc Guest

    Mass copy of data in recordset to another recordset


    I am writing a program that needs some audit trail and therefore there is
    a need
    to copy whatever data from 1 recordset to another recordset. I am looking
    for
    a better method of do it instead of the long method of going through every
    field
    (!fieldname) in 1 recordset and assigning it to the corresponding field in
    the other
    recordset.

    Anyone has any ideas of how to go about??

  2. #2
    Frank Guest

    Re: Mass copy of data in recordset to another recordset


    If you are really working with a lot of data, you could try to make the first
    recordset persistent with the Save method. This writes the recordset content
    to a file. When you close the recordset, that file is closed too. Then, you
    can use a second recordset and call to Open method setting the Source parameter
    to the name of the file.

    Depending on the amount of data, you could raise performance although the
    disk operation.

    Good lucky,

    Frank

    "Alpc" <alpc@geocities.com> wrote:
    >
    >I am writing a program that needs some audit trail and therefore there is
    >a need
    >to copy whatever data from 1 recordset to another recordset. I am looking
    >for
    >a better method of do it instead of the long method of going through every
    >field
    >(!fieldname) in 1 recordset and assigning it to the corresponding field

    in
    >the other
    >recordset.
    >
    >Anyone has any ideas of how to go about??



  3. #3
    Clint Guest

    Re: Mass copy of data in recordset to another recordset


    I saw an example where the recordset was saved (holus bolus) in a property
    bag and then reloaded into another rst variable.
    It was actually headed under 'Cloning' and is different to .Clone witch is
    only a clone of bookmarks and record position and a genuine (Sheep style)
    copy.
    Only one catch is that it must have a client side cursor, which is not
    always possible. eg when you return multiple recordsets from a Stored Proc
    they're always Server Side and forwardonly. One big pain in the proverbial.

    Anyone has better way, pls let me know!

    Clint

    "Alpc" <alpc@geocities.com> wrote in message
    news:398e72e0$1@news.devx.com...
    >
    > I am writing a program that needs some audit trail and therefore there is
    > a need
    > to copy whatever data from 1 recordset to another recordset. I am looking
    > for
    > a better method of do it instead of the long method of going through every
    > field
    > (!fieldname) in 1 recordset and assigning it to the corresponding field in
    > the other
    > recordset.
    >
    > Anyone has any ideas of how to go about??




  4. #4
    Shelly Rosenfeld Guest

    Re: Mass copy of data in recordset to another recordset


    I may be outta line here, since I've yet to
    try this. But honest! I hope to implement this
    concept this year, ok?

    .. Have a shadow table of EVERY user table

    .. Add a trigger to each table on Insert/Upd/Del etc.
    to save from Inserted/Updated/Deleted to the shadow
    You will have ALOT more data storage, but you can
    audit to the second and user to trace which change
    caused something...

    So, am I crazy, or do I have the right idea?

    Sheldon


    Alpc <alpc@geocities.com> wrote in message news:398e72e0$1@news.devx.com...
    >
    > I am writing a program that needs some audit trail and therefore there is
    > a need
    > to copy whatever data from 1 recordset to another recordset. I am looking
    > for
    > a better method of do it instead of the long method of going through every
    > field
    > (!fieldname) in 1 recordset and assigning it to the corresponding field in
    > the other
    > recordset.
    >
    > Anyone has any ideas of how to go about??




  5. #5
    Russ Guest

    Re: Mass copy of data in recordset to another recordset


    "Alpc" <alpc@geocities.com> wrote:
    >
    >I am writing a program that needs some audit trail and therefore there is
    >a need
    >to copy whatever data from 1 recordset to another recordset. I am looking
    >for
    >a better method of do it instead of the long method of going through every
    >field
    >(!fieldname) in 1 recordset and assigning it to the corresponding field

    in
    >the other
    >recordset.
    >
    >Anyone has any ideas of how to go about??


    Alpc,
    How about:

    set rs2 = rs1.Clone

    HTH,
    -Russ.


  6. #6
    Alpc Guest

    Re: Mass copy of data in recordset to another recordset


    Yes, that is the concept I have in mind. However, I am looking for
    the method of copying the data for each record from the actual
    table to the shadow table. As there are many fields in the table,
    I do not want to write a function that transfer each and every field
    (using the recordset2!fieldname = recordset!fieldname method)
    to the shadow table. I was also hoping to make it generic so that
    I could just pass in the recordsets and the tablename and the
    function will do the trick for whatever table that I need to shadow on.

    I don't think Clone will work as I am writting to another table with
    additional fields that I need to include. (Or at least I have not seen
    a Clone example that would help in this problem)

  7. #7
    Clint Guest

    Re: Mass copy of data in recordset to another recordset

    Looks like it won't work, but it does...

    Dim p As New PropertyBag

    p.WriteProperty "Tmp", rs1
    Set rs2 = p.ReadProperty("Tmp")

    Presto! A genuine copy.



  8. #8
    Matthew Solnit Guest

    Re: Mass copy of data in recordset to another recordset

    A slightly better solution is to loop through the Fields collection, and
    assign values to columns that way. You can't use For Each with multiple
    collections, unfortunately.

    Dim i As Long
    With rsSource
    Do While Not .EOF
    With .Fields
    For i = 0 To (.Count - 1)
    rsDest.Fields(i).Value = .Item(i).Value
    Next
    End With
    .MoveNext
    Loop
    End With

    Notes:
    - This is much faster than referencing each field by name
    - It requires the two tables to have the same columns, in the same order
    - using With makes a big difference

    You could also experiment with using Field objects, which some people say is
    the fastest way of all:

    Dim fldSrcCustomerNo As ADODB.Field
    Dim fldDstCustomerNo As ADODB.Field
    'etc...

    Set fldSrcCustomerNo = rsSource.Fields("Customer_No")
    Set fldDstCustomerNo = rsDest.Fields("Customer_No")

    Do While Not rsSource.EOF
    fldDstCustomerNo.Value = fldSrcCustomerNo.Value
    Loop

    "Alpc" <alpc@geocities.com> wrote in message
    news:398f7375$1@news.devx.com...
    >
    > Yes, that is the concept I have in mind. However, I am looking for
    > the method of copying the data for each record from the actual
    > table to the shadow table. As there are many fields in the table,
    > I do not want to write a function that transfer each and every field
    > (using the recordset2!fieldname = recordset!fieldname method)
    > to the shadow table. I was also hoping to make it generic so that
    > I could just pass in the recordsets and the tablename and the
    > function will do the trick for whatever table that I need to shadow on.
    >
    > I don't think Clone will work as I am writting to another table with
    > additional fields that I need to include. (Or at least I have not seen
    > a Clone example that would help in this problem)




  9. #9
    Alpc Guest

    Re: Mass copy of data in recordset to another recordset


    Thanks... yes I was thinking along the line of fields collection, just did
    not
    have an idea of how to go about. As for field objects... I don't quite understand
    the "Customer_No". Does this means that by setting the first field it will
    take
    the rest of the fields in the recordset?

    Also, I have heard that .AddNew is slower than an SQL insert command. Is
    that
    true? If so, and I want to implement using INSERT command, I got to list
    the
    entire fields for insertion??? (That's what I understand from all the SQL
    commands
    that I know of) I guess that will also means throwing the thought of using
    a generic
    function away.....

    "Matthew Solnit" <nospam@nospam.com> wrote:
    >A slightly better solution is to loop through the Fields collection, and
    >assign values to columns that way. You can't use For Each with multiple
    >collections, unfortunately.
    >
    >Dim i As Long
    >With rsSource
    > Do While Not .EOF
    > With .Fields
    > For i = 0 To (.Count - 1)
    > rsDest.Fields(i).Value = .Item(i).Value
    > Next
    > End With
    > .MoveNext
    > Loop
    >End With
    >
    >Notes:
    >- This is much faster than referencing each field by name
    >- It requires the two tables to have the same columns, in the same order
    >- using With makes a big difference
    >
    >You could also experiment with using Field objects, which some people say

    is
    >the fastest way of all:
    >
    >Dim fldSrcCustomerNo As ADODB.Field
    >Dim fldDstCustomerNo As ADODB.Field
    >'etc...
    >
    >Set fldSrcCustomerNo = rsSource.Fields("Customer_No")
    >Set fldDstCustomerNo = rsDest.Fields("Customer_No")
    >
    >Do While Not rsSource.EOF
    > fldDstCustomerNo.Value = fldSrcCustomerNo.Value
    >Loop
    >


  10. #10
    Friendly response Guest

    Re: Mass copy of data in recordset to another recordset


    "Shelly Rosenfeld" <ShellyRo@worldnet.att.net> wrote:
    >
    >I may be outta line here, since I've yet to
    >try this. But honest! I hope to implement this
    >concept this year, ok?
    >
    >.. Have a shadow table of EVERY user table
    >
    >.. Add a trigger to each table on Insert/Upd/Del etc.
    > to save from Inserted/Updated/Deleted to the shadow
    > You will have ALOT more data storage, but you can
    > audit to the second and user to trace which change
    > caused something...
    >
    >So, am I crazy, or do I have the right idea?
    >


    >Sheldon


    >
    >
    >Alpc <alpc@geocities.com> wrote in message news:398e72e0$1@news.devx.com...
    >>
    >> I am writing a program that needs some audit trail and therefore there

    is
    >> a need
    >> to copy whatever data from 1 recordset to another recordset. I am looking
    >> for
    >> a better method of do it instead of the long method of going through every
    >> field
    >> (!fieldname) in 1 recordset and assigning it to the corresponding field

    in
    >> the other
    >> recordset.
    >>
    >> Anyone has any ideas of how to go about??

    >
    >


    I Dont believe that the triggers would be a very good idea in the fact the
    the whole point of this is to leave an audit trail.
    by causing the data to be copied on any insert/update/delete etc.
    your are causing an exact copy of the database no MATTER WHAT!

    so if the data is corrupted or changed on purpose so is the audit trail and
    that leaves you with no audit.

    you might want to write a stored proc to fire when the app closes or if the
    server is always on then fire at midnight
    this way it copies the data as is and will not be affected by table events
    which will bog down the server and cause major lag!!

    think of a copany like ATT who does millions of transaction's a day and then
    makes a complete copy on every transaction the time that would waste would
    be tramendous.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center