I need to change an existing column in a table so that it has the identity property using T-SQL and then insert data into this table. The column already contains data and after this insertion, I must again change this column by disabling the identity property. Currently I need to enter the data manually, seeing which is the last integer (index) to complete the insertion. How to do this correctly?
Change column for indentity using T-SQL in SQL Server
5
asked by anonymous 15.12.2015 / 15:35
3 answers
6
I think your question is still a bit confusing, you are not presenting your real problem and starting from a solution that may be inadequate. If the field is marked with IDENTITY it is because it must have some importance of integrity or referential for some external relation.
However, if you want to perform a INSERT by manually setting this value either by manually incrementing or by filling in gaps that may have been left after some records have been deleted you should use the following statement:
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)
15.12.2015 / 17:45
4
If you insert an id automatically and then re-insert 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 }
15.12.2015 / 17:39
3
Rodrigo, as far as I know, can not make this change without excluding and re-creating the column. And to do this via script and keep existing data I think it would be impractical.
It would not be easier, when the code is not sent by the user, do you make a function that returns the ID + 1 and write the record normally?
15.12.2015 / 17:27