Wednesday, October 26, 2016

How to insert Identity values in the Non-Identity column in SQL Server?

Here I am going to discuss how to insert identity value in the non-identity column.

For this, I have made a table. In which I have used the datatype corresponding to the column.

The table does not have any identity, primary, or any other type of key column attached to it.

Now I want to insert the value in the table's Id column. And also I want that there should not be any duplicate records added in the value of the Id column.

Now I am using the below query to insert an auto-increment value in the table's Id column.

Select ISnull((Select MAX(SalaryID) from Salary),0)+1

Let's create a simple table using the below command:

Create Table Salary
SalaryID int,
Amount numeric(10, 2),
EmployeeID int

As you see, there is no auto-increment column in the above table. Now I want to insert an auto-increment identity value in the SalaryID column. I have used the insert query to insert values in the table.

Now I am using the following insert query to insert value in the salary table.

INSERT INTO Salary(SalaryID,Amount,EmployeeID) 
VALUES (ISnull((Select MAX(SalaryID) from Salary),0)+1,1000,1)

No comments:

Post a Comment