Change column to indentity using T-SQL in SQL Server

Asked

Viewed 2,237 times

5

I need to change an existing column in a table so that it stays with the property identity using T-SQL and then entering data into this table. The column already contains data and after this insertion, I must again change this column by disabling the property identity. Currently I need to enter the data manually, seeing which is the last integer (index) to complete the insertion. How to do this correctly?

sql sql-server tsql

  • I didn’t understand you put the id one by one or that it is automatically generated?

  • 1

    I want to be automatically generated one by one from the last added, and also at the end of the code do not automatically generate.

  • 1

    If that’s what I’m getting at this hereSET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

3 answers

6


I think your question is still a little confused, you are not presenting your real problem and starting from a solution that may be inadequate. If the field is marked with IDENTITY is because it must have some integrity or referential importance to some external relationship.

However, if you wish to perform a INSERT manually setting this value either by manually incrementing or filling gaps that may have gotten left after some records were deleted you should use the following instruction:

SET IDENTITY_INSERT [SUA_TABELA] ON;
GO

INSERT INTO [SUA_TABELA] (ID, Campo, ...) VALUES (4, 'Valor', ...)
GO

SET IDENTITY_INSERT [SUA_TABELA] OFF; 
GO

Reference: SET IDENTITY_INSERT (Transact-SQL)

  • 1

    just to complement SET IDENTITY_INSERT [SUA_TABELA] OFF;
GO ; in the end

  • Edited, Thanks @Marconciliosouza

4

If you want to enter an id automatically and then re-enter one by one you have to use SET IDENTITY INSERT (SQL Server Compact).

It allows explicit values to be inserted into the identity column of a table. Syntax

SET IDENTITY_INSERT table { ON | OFF }

More details here

3

Rodrigo, as far as I know, there’s no way to make this change without excluding and recreating the column. And to do this via script and keep the existing data I think would be unworkable.

It would not be easier, when the code is not sent by the user, you make a function that returns the ID + 1 and write the record normally?

  • 1

    I’ll do it anyway I’ll keep looking, but it would make it really easy to have some way to do it. Thanks

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.