Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    157

    Unanswered: copying multiple rows into the same table with new primary key supplied?

    ei guys,


    question, how do i insert multiple rows, (around 40, perhaps) into the same table)
    with new primary keys eg.

    existing rows

    (primary keys being field 1 and field 2)

    field 1 field2 field 3

    00 a data1
    00 b data2
    00 c data3

    rows to insert to look like this

    01 a data1
    01 b data2
    01 c data3
    Only quitters quit!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure i understand the question

    just go ahead and do the insert as normal
    Code:
    INSERT
      INTO yourtable
         ( field1, field2, field3 )
    VALUES
         ( 01, 'a', 'data1' )
       , ( 01, 'b', 'data2' )
       , ( 01, 'c', 'data3' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    Posts
    157
    I just felt there must be some way for me to copy something like a thousand rows, with 8 - 10 fields each

    into a new set of thousand rows with different primary keys from the previous, but exactly the same value in other fields
    Only quitters quit!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's possible, but where would the different primary key values come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    157
    i can just append something to the existing primary key... but i'm confused as to how the form of this query would be... hope you won't mind giving me that?
    Only quitters quit!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    INSERT
      INTO yourtable
         ( field1
         , field2
         , field3 )
    SELECT 01
         , field2
         , field3
      FROM yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Posts
    157
    aha, i think i see where my problem was

    i was using INSERT INTO table () values (SELECT ...);

    many thanks, i guess i'm still a big baby with query manipulation, bad dragdown habits from MSAccesss still here
    Only quitters quit!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot say INSERT INTO table () values (SELECT ...) in msaccess either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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