Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy / move records between tables (A2K SR1)

    How does one copy/move one record from a linked table A to another linked table B using SQL? Both tables have the same structure.

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy / move records between tables (A2K SR1)

    Here is the SQL for copying the records
    <pre>INSERT INTO TableB SELECT * FROM TableA;</pre>

    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

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

    Re: Copy / move records between tables (A2K SR1)

    (1) If you want to copy one specific record, you'll have to add a WHERE condition to the SQL Bryan Carbonell posted. The precise form of the WHERE condition depends on how you identify the record to be copied.
    For instance:

    INSERT INTO TableB SELECT * FROM TableA WHERE RecordID=222;

    (2) To move a record, copy it first and then delete it. This has to be done in two separate SQL statements.
    For instance:

    INSERT INTO TableB SELECT * FROM TableA WHERE RecordID=222;

    DELETE FROM TableA WHERE RecordID=222;

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy / move records between tables (A2K SR1)

    Quite right about the WHERE.

    I missed that when I read the post. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Sorry.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy / move records between tables (A2K SR1)

    As HansV said, to move, Copy, then Delete.
    I'd just add that, this is best done within a transaction. Otherwise if the Delete fails for any reason, you've got a mess with your data.

    Regards,
    jim

Posting Permissions

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