In general, you don't insert data manually in a table which has an
identity column. However, in obvious cases if you need to insert data into
the table manually then writing general inset Query will failed with error:
For example, you have a table (DDL) like below:
Create table PurchaseOrder
(orderId int identity(1,1),
item nvarchar(100))
If you would like to insert data into the table,
standard syntax for inserting data is like below:
Insert into PurchaseOrder(orderId,item)
values (101,'strawberry')
However, above query will failed with below error
message:
Fig 1: Eror Inserting data into the table with Identiy column |
To insert the data into a table with identity column
you should not put any value for the identity column. e.g.
values ('strawberry')
And then you will see the result set like below:
Fig 2. Output of the above insert command |
However, if you would like to explicity put value for the
identity column. e.g. you would like to add a negative number as orderid to
make an item invalid. (sometimes you may need for the data warehouse dimension
and fact tables). SQL for inserting data with your own choice:
SET IDENTITY_INSERT PurchaseOrder ON
insert into PurchaseOrder(orderId,item)
values (-1,'An Island')
SET IDENTITY_INSERT PurchaseOrder OFF
Now the result will look like below:
Fig 3: Output after the above SQL command |
No comments:
Post a Comment