Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: identity_insert?

    I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
    I am using:
    Code:
    set identity_insert soldVehicles on
    INSERT INTO soldVehicles 
    SELECT *
    FROM vehicles
    Where sent2sold = 'yes'
    but I get this error:
    Error -2147217900


    An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    set identity_insert soldVehicles on
    INSERT INTO soldVehicles
    SELECT *
    FROM vehicles
    Where sent2sold = 'yes'
    As I have turned ID_insert ON it must be the column list?...
    not sure what to do next.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good assumption -- the error message actually says that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Are you sure both table structures are identical?
    An insert without an explicit column list is never a good idea, just as a SELECT * should not be used for this. SELECT * does not necessarily return the columns in the order expected by an unqualified INSERT INTO.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Yes, that's correct. When the identity_insert option is set to True, you must specify the column list.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Having just read your question again, I think you are taking the wrong approach. I don't see why you need to explicitly turn on identity_insert. If you need an identity column for the soldVehicles which will contain values that are distinctly different from the identity values in the original table, then there is no need to use the identity_insert option.

    If on the other hand, you only want a single identification column in the soldVehicles table which will have values corresponding to those in the vehicles table, then there is no need to use neither an identity column nor hence the identity_insert option. You would just create a standard column to represent the vehicle ID numbers from the original table and insert the values as you would with any other insert operation.

    In the following code sample, constraint and index definitions have been specified in shorthand notation for convenience. In a production system, I recommend proper naming of all objects.

    Code:
    create table vehicles
    (
      vehicleID identity(1,1) not null unique,
      columnA int not null
    )
    
    create table soldVehicles
    (
      vehicleID int not null unique,
      columnA int not null,
      foreign key vehicleID references vehicles (columnA)
    )
    
    insert into soldVehicles (vehicleID, columnA)
    select 
      v.VehicleID, v.ColumnA
    from
      vehicles v
    Although this method is easy to implement and can indeed provide the functionality that you have described, are you not just after a column to mark whether or not a vehicle has been sold? Do you really need an extra table?
    Last edited by r123456; 11-11-07 at 07:37.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks guys,
    As Rudy well knows, I am a DB dunce and my old tables are shockingly simplistic and er...large.
    To help out in the short-term I am trying to strip out and archive as much unused data as possible, rather than just marking it.
    The new ID doesn't need to relate to its old value.
    Looks like I am going to have to explicitly write out the list of column names.
    Last edited by darkmunk; 11-11-07 at 08:10.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Are you from Cornwall?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    more or less, Devon now

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Oh yes. It is lovely down there.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by darkmunk
    Looks like I am going to have to explicitly write out the list of column names.
    Which is exactly what the error message told you to do.

  11. #11
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    listen guys, when a person knows jack about databases, an error message like that is still pretty cryptic. I still didn't know what to do next, OK?
    Robert chose to help, and now I know what to do.

    these 'stating the obvious' replies don't help anyone.

    In any case I have learnt not to trust error messages explicitly.
    Last edited by darkmunk; 11-12-07 at 05:48.

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by darkmunk
    these 'stating the obvious' replies don't help anyone.
    I believe that comment was referring to you Ivon.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks Robert.
    I am trying to implement an insert similar to your example but I am getting a syntax error.
    Code:
    INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05) VALUES (SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes')
    GO
    Incidentally, what does the V. stand for in your example? do I need that to do this sort of batch insert?
    Last edited by darkmunk; 11-12-07 at 06:55.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When using a SELECT to provide the values for an INSERT the VALUES keyword is not allowed.
    Code:
    INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05) 
    SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes'
    GO
    See the manual for details.

  15. #15
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    excellent! that's cracked it.
    Thanks shammat

Posting Permissions

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