CODEWITHSUNDEEP
sqlsql-server
aleczandru
aleczandru

Reputation: 5459

Setting an existing column to auto-increment

HI I have an existing table witch has a column ExamQuestionsAnswersDocumentId.This column is set as a primary key and has around 60000 rows of data.

The type of the column is int and it has a unique id for each row.The rows were transfered from another table , from another db and we needed to preserve the exact id values in the new table.

This is what I did so far:

CREATE TABLE ExamQuestionsAnswersDocuments(
ExamQuestionsAnswersDocumentId int NOT NULL,
RootStorageId int NOT NULL,
StorageFileName varchar(200),
OriginalFileName varchar(200),
Title varchar(200),
Deletion_Date datetime,
Creation_Date datetime,
Modification_Date datetime,
RowVersion int,
CreatedBy int,
ModifiedBy int,
DeletedBy int,
CONSTRAINT FK_ExamQuestionsAnswersDocuments_FileShareRootStorage 
FOREIGN KEY (RootStorageId) REFERENCES FileShareRootStorage(FileShareRootStorageId)

)

After that I have runed this script in order to add the data to the new table:

 INSERT INTO [LocalServerB].[dbo].[ExamQuestionsAnswersDocuments] (ExamQuestionsAnswersDocumentId , RootStorageId , StorageFileName , OriginalFileName , Title , Deletion_Date , Creation_Date , Modification_Date , [RowVersion] , CreatedBy , ModifiedBy , DeletedBy)
SELECT d.DocumentId     as ExamQuestionsAnswersDocumentId,
       2                as RootStorageId,
       d.RealName       as StorageFileName,
       d.FileName       as OriginalFileName,
       d.Title          as Title,
       d.Deletion_Date  as Deletion_Date,
       d.Creation_Date  as Creation_Date,
       d.Modification_Date as Modification_Date,
       d.[RowVersion]          as [RowVersion],
       d.CreatedBy             as CreatedBy,
       d.ModifiedBy            as ModifiedBy,
       d.DeletedBy             as DeletedBy
FROM [LocalServerA].[dbo].[Sync_Exams] as e 
JOIN [LocalServerA].[dbo].[Documents]  as d ON d.DocumentId = e.QuestionsDocumentID OR
                                     d.DocumentID = e.AnswersDocumentID

So far so good now I have to set the ExamQuestionsAnswersDocumentId as the primary key:

ALTER TABLE ExamQuestionsAnswersDocuments
ADD CONSTRAINT PK_ExamQuestionsAnswersDocumentId PRIMARY KEY CLUSTERED (ExamQuestionsAnswersDocumentId)
GO

Now as the last part I need to set the column to auto-increment every time I add a new row. This is preety easy with SSMS but I will need the script to run it on are actual DB.

All the solution I find online say that this is not posible , that the column will be regenarated , but there must be a way to be able to set it as an IDENTITY AUTO INCREMENT without the column to be regenerated.

I have tryed doing it with SSMS and nothing get's regenerated even after I add some new rows.The interesting this is that it continues incrementig from the the highest Id it finds.

Does anyone know how to set a column as an AUTO INCREMENT Identity?

Upvotes: 0

Views: 1684

Answers (1)

podiluska
podiluska

Reputation: 51514

Set it to be an identity when you create the table then use 

 set Identity_Insert ExamQuestionsAnswersDocuments on

before inserting your data, then 

 set Identity_Insert ExamQuestionsAnswersDocuments off

afterwards. eg:

CREATE TABLE ExamQuestionsAnswersDocuments(
    ExamQuestionsAnswersDocumentId int NOT NULL identity(1,1),
    RootStorageID int,
    ...
)

set identity_insert ExamQuestionsAnswersDocuments on

insert ExamQuestionsAnswersDocuments(ExamQuestionsAnswersDocumentId, RootStorageID, ...) 
Select ...

set identity_insert ExamQuestionsAnswersDocuments off

Upvotes: 2

Related Questions