Solved

Small Identity and PK script

Posted on 2006-10-24
2
284 Views
Last Modified: 2012-06-27
Hi guys,

I've got a table in which I have two columns, ID and URL. I would like to have a script which can set the ID column to be the PK and enable an identity starting from 1 increasing with 1.

NOTE: The table already contains 5000 records which I don't want to loose.

Can anyone help me with this?

Thanks!

Wim Toremans
0
Comment
Question by:Wimmeke
2 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 17794418
Hi,

I assume the ID column already has values in  - do you want to keep those ID values?
0
 
LVL 10

Accepted Solution

by:
AaronAbend earned 125 total points
ID: 17794421
create a new table with an incremental id, set identity_insert to allow inserts of old records, update those records to use negative numbers, then turn identity_insert off and you are ready to go

create table newtable (id int identity(1,1), url varchar(1234))

set identity_insert  newtable on

insert newtable (id, url) select oldid, oldurl from oldtable

update newtable set id=id*-1 (to use negative numbers for the old records)

set identity_insert  newtable off
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question