How to insert or update primary key (int) ?!

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  May 22, 2014 at 2:40 am

  #291654

  Hi all,

  i'm beginner with t sql and sql server 2008.

  i have a problem and i don't know how to solve it and how to do that 🙂

  since ten years perhpas the society has a database with some table (person, country, and so on)

  since last years , the project manager decides to create a new database with some new design 🙂

  because before some information was save in one table 🙁

  well, the problem is when we pass all application to the new model we've notice that some id are not the same !!!!!

  sample :

  table Language old model

  -------------------------

  id frenchDescription english description

  ----------------------------------------

  3 Anglais English

  .....

  ......

  table Language new model

  id frenchDescription english description

  -----------------------------------------

  5 Anglais English

  .....

  .....

  Alrigth , you can understand that the new model must be the same id => 3

  my question is how to modify id on the table ?

  thanks for your time and your knowledge

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 22, 2014 at 2:45 am

  #1715595

  Is the column defined as an identity column?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  May 22, 2014 at 4:45 am

  #1715630

  Please check you table structure if any identity property applied on id column

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 22, 2014 at 5:06 am

  #1715641

  the other folks have undoubtedly nailed int; if you needed to migrate those exact values you'd need to use IDENTITY_INSERT and insert the existing values explicitly.

  SET IDENTITY_INSERT NewTable ON

  INSERT INTO NewTable([id] [frenchDescription] [english description])

  SELECT [id] [frenchDescription] [english description]

  FROM OldTable

  SET IDENTITY_INSERT NewTable OFF

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  May 23, 2014 at 12:58 am

  #1715952

  Hi all,

  yes we must to have the same id value on the last model and new model

  thanks for your script, i've try this today.

  Have a nice day

  Christophe