Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: insert primary key column

    I am trying to add a new column into a table that is a copy of another table. it then needs to become the primary key of the new table and also make it an auto number, is this possible?
    the SQL code i already have in the query for copying the data to the different table is shown below:

    SELECT * INTO admissions1
    FROM SATscores;

    Any help would be appreciated

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    hello mark

    once a field is used for anything else then a primarykey it can´t be used to setting it as an autonumber field, so setting it as a primarykey is no problem but the autonumber is. if in the primaryfield contains duplicates you first have to eliminate them because of the uniqueness property of the primarykey

    regards emiel

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Hi thanks for the reply, the field that i want to be a primary key will be a new field, so will have no data in it at all, i just want it to make it an auto number. THis should mea it has no duplicates any way, so is there SQL code that can insert my new row with the auto number property?



    Originally posted by emiel
    hello mark

    once a field is used for anything else then a primarykey it can´t be used to setting it as an autonumber field, so setting it as a primarykey is no problem but the autonumber is. if in the primaryfield contains duplicates you first have to eliminate them because of the uniqueness property of the primarykey

    regards emiel

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Create the autonumber field first, then insert all of your data. The PK values will be automatically generated. The only problem is you will have to explicitly define your fields in the INSERT query.

    eg:

    T1:
    PKfield - AutoNumber
    data1 - string
    data2 - number

    T2
    data1 - string
    data2 - number

    INSERT INTO T1 (data1, data2)
    SELECT data1, data2
    FROM T2

    By leaving the autonumber field out of the INSERT query, you are implicitly allowing it to auto-inc with each new record.

Posting Permissions

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