title: SQL Server : duplicate rows with ID < 0 and get new created ID
body: <p>I have recovered a table from MS Access inside SQL Server 2008 R2.</p>
  
  <p>The old table in MS Access was using a PK with replication type integer (so there's negative values)</p>
  
  <p>After importing the table into SQL Server 2008 R2, I have changed the PK for an integer identity column.</p>
  
  <p>What I'm trying to do now is to make a script that will take all the row with negative PK, duplicate that row with a new PK (autonumber) and change the value of other table FK (negative values) with the newly created PK (autonumber). Then, finally, delete the OLD row that was just duplicate.</p>
  
  <p>Thanks for the help</p>
  
  <p>This is an example of the table :</p>
  
  <pre><code>CREATE TABLE [dbo].[TESTCopy](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Field1] [nchar](10) NOT NULL,
  [Field2] [nchar](10) NOT NULL,
  [Field3] [nchar](10) NOT NULL,
  CONSTRAINT [PK_TESTCopy] PRIMARY KEY CLUSTERED 
  (
  [ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>