Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Append to table (Access/SQL Server)

    This is more a double check.

    I have to append 2.5 million credit/ debit rent payments records from Access to a SQL Server every Monday. The tables are grouped by tenancy reference. Every other day of the week I have to append to the table from an excel sheet to the SQL Rents table. Due to the way the data is presented on a web page can I confirm for definite that when I append to a table it will always append to the bottom of the recordset and not the top please?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to table (Access/SQL Server)

    In a relational database, records in a table do not have an intrinsic order. The default order in which the records are displayed is that of the primary key (if there is one), and of course you can present the data in different sort orders in a query/view.

    Without specifying a sort order, the concept "bottom" or "top" has no real meaning.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append to table (Access/SQL Server)

    Thanks Hans

    This is where my conundrum has started seeds of doubt in my head. The data is set out as follows:

    tenantsref,entrydate,debit,credit,balance,desc

    My extracts are from a flat file legacy system which provides me with the calculated balance and looks vaguely like a bank statement. When I append the data SQL table adds a field called WebRentID which effectively becomes the tables primary key so that it can relate to a tenancy table using tenantsref as the foriegn key. The order of the records are important as it cannot necessarily be sorted by date as various payments get credits/debits and are back dated but show as the affective date in the entrydate field. To get the data in the statement to match in some for of chronology they will become nonsensical.

    I am therefore assuming that as a primary key is being created on upload they will appear as an incremental rises in the PK and therefore I can sort on PK.... It has so far but this is a one off test so far and I wanted to make sure for subsequent appends
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to table (Access/SQL Server)

    If WebRentID is an AutoIncrement type field (the equivalent of AutoNumber), you should be OK.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append to table (Access/SQL Server)

    It is and I feel happy for your reassurrance.
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •