Last post Jun 26, 2018 09:07 AM by Abraham Qian
Jun 25, 2018 06:01 AM|nambir|LINK
I will be having a table with 3 columns:(for simplicity purpose)
1)ID- It is a GUID
tempId column will incremented by 1 for each row.
We have one business logic, for that we have to empty the tempId for the security purposes.
But if we keep the tempId as identity column , i found that we cant update the field to null.
Can you suggest what are all the different ways to handle it?
one of my colleague told me to use the SP to get the max of tempId and increment by 1 and use that in C# to insert it.
Jun 25, 2018 06:19 AM|dilipvision|LINK
remove identity from "tempId" column.
Declare @NextTempId int
Select @NextTempId = max(isnull(tempId,0)) + 1 from table.
use @NextTempId to insert in table.
Jun 26, 2018 09:07 AM|Abraham Qian|LINK
As far as I know, as the identity column of the database, we cannot set Allow Nulls= true. because it is the identification column, used to identify the only record.
For security purposes, Guid could be used as an Identity column. I suggest that you export this data migration to another table, using the following SQL statement.
Please ensure that the column data types are the same.
ALTER TABLE table1 ADD [column1] INT,[column2] nvarchar(50)
INSERT INTO table1 ([column1],[column2])
SELECT [column1],[column2] FROM table2
I have made a demo, wish it is useful to you.
Products Table Design.
CREATE TABLE [dbo].[Products] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NOT NULL,
[Price] INT NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
Table1 Table Design.
CREATE TABLE [dbo].[Table1]
[Guid] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid()
ALTER TABLE Table1 ADD [ProductName] nvarchar(50),[ProductPrice] int
INSERT INTO Table1 ([ProductName],[ProductPrice])
SELECT Name,Price FROM Products
How it works.
Feel free to let me know if you have any question.