Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have a table in Access with an autonumber field and a table in SQL Server with exactly the same fields although the autonumber field in SQL Server is an Identity field.

    When i empty the SQL Server table then append all fields from the Access table i get errors saying there are duplicated entries, is this because i am trying to copy the autonumber field from Access to an Identity field in SQL Server.

    How can i overcome this problem?

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Patt,

    I would guess you do not want to lose the ID from Access because other data is linked to it. You may need to add an extra column to your SQL Server table for 'old ID', and import the Autonumber data into this field. Then, run update queries on your linked tables to replace the old ID with the new ID. A query that includes the two tables, with a link between the foreign key field in your 'slave' table and the 'old ID' field in the master table, is one approach. Have the query update the foreign key field to the new SQL Server Identity value.

    A bit longwinded, and must be repeated for every linked table, but it works.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='JulesG' post='782769' date='03-Jul-2009 03:51']Patt,

    I would guess you do not want to lose the ID from Access because other data is linked to it. You may need to add an extra column to your SQL Server table for 'old ID', and import the Autonumber data into this field. Then, run update queries on your linked tables to replace the old ID with the new ID. A query that includes the two tables, with a link between the foreign key field in your 'slave' table and the 'old ID' field in the master table, is one approach. Have the query update the foreign key field to the new SQL Server Identity value.

    A bit longwinded, and must be repeated for every linked table, but it works.[/quote]
    Thanks Jules, I have done that kind of thing before, marrying up an old ID to the new one created by either a Autonumber field or an IDentity field.

    I was wondering though if you cannot enter a value in the IDentity field of a SQL Server table. I seem to recall you can do it in Access, i may be wrong there though.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Actually you can do what you describe Pat, but it requires that either the table be a new table in SQL Server (i.e. the Identity property still thinks there are no rows in the table), or you have to take the Identity property off (the field type in SQL Server needs to be Integer), run the append, and then put the Identity property back. As long as the Identity property doesn't think that a value being entered hasn't been used already, and it is greater than the largest value, then you can append with the property set. I've not tested that with SQL Server 2008, but I know it works with 2000 and 2005.
    Wendell

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WendellB' post='782872' date='04-Jul-2009 00:48']Actually you can do what you describe Pat, but it requires that either the table be a new table in SQL Server (i.e. the Identity property still thinks there are no rows in the table), or you have to take the Identity property off (the field type in SQL Server needs to be Integer), run the append, and then put the Identity property back. As long as the Identity property doesn't think that a value being entered hasn't been used already, and it is greater than the largest value, then you can append with the property set. I've not tested that with SQL Server 2008, but I know it works with 2000 and 2005.[/quote]
    Thanks Wendell, i will do that to preserve my autonumber fields from access. This is a one off job so that is the way to go.

    Is there any way to kid SQL Server 2005 to think it never had rows in that particular table?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='patt' post='782949' date='03-Jul-2009 18:17']... Is there any way to kid SQL Server 2005 to think it never had rows in that particular table?[/quote]
    Not that I've been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WendellB' post='783166' date='06-Jul-2009 15:08']Not that I've been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.[/quote]
    I can see i have some testing to do.

    I will let you know how i go.

    Thank you.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WendellB' post='783166' date='06-Jul-2009 15:08']Not that I've been able to find. In fact, if the identity property is turned off, the table saved, then the rows appended, and then the Identity property turned back on for the field in question, new rows will (try) to be appended starting with the next value that would have been used before the identity property was turned off. That works in many cases, but I have seen it cause problems as well. So I usually delete the field and add a new one.[/quote]
    Hey Wendell, thanks for your help,

    I think I found a command i can use in this case, it is:

    SET IDENTITY_INSERT tablename ON
    .....do my inserts into the table tablename
    SET IDENTITY_INSERT tablename OFF

    So i dont need to take off the identity from a table's field, do my inserts then put it on again afterwards.

    I hope i am right.

Posting Permissions

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