SQL Server Fun - 4 :: How to insert duplicate values to identity column

Question

How to insert duplicate values to identity column.
Is it possible? If Yes, how?

Abhimanyu K Vatsa · Accepted Answer

i have created a sample table, find the sql query below
/****** Object:  Table [dbo].[Table1]    Script Date: 06/04/2011 04:08:00 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U')) DROP TABLE [dbo].[Table1] GO /****** Object:  Table [dbo].[Table1]    Script Date: 06/04/2011 04:08:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Table1]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) END GO SET IDENTITY_INSERT [dbo].[Table1] ON INSERT [dbo].[Table1] ([id], [name], [date]) VALUES (1, N'akv', N'slfj') INSERT [dbo].[Table1] ([id], [name], [date]) VALUES (2, N'lfjs', N'sdlfj') INSERT [dbo].[Table1] ([id], [name], [date]) VALUES (3, N'sdlfj', N'sdlfj') INSERT [dbo].[Table1] ([id], [name], [date]) VALUES (4, N'sdfj', N'sdlfj') INSERT [dbo].[Table1] ([id], [name], [date]) VALUES (5, N'sdf', N'sdlfj') SET IDENTITY_INSERT [dbo].[Table1] OFF
Even if you only execute the following command anytime to create any table Is_identity.
SET IDENTITY_INSERT tablename ON SET IDENTITY_INSERT tablename OFF
Keep in mind:
(i) When it is enabled on a table you must specify a value for the identity column.
(ii) It can only be enabled on one table at a time.  If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error, it will cause error at runtime of project.
(iii) The user issuing the statement must own the object, be a system administrator.

Suthish Nair · Answer

So this is also an example between Primary key and Identity.

Vilas Gite · Answer

I learned by Fun
set identity_insert rd on
INSERT [dbo].[rd] ([prod_id], [prod_desc], [Remark]) VALUES (7, 'cap', 'Black')
INSERT [dbo].[rd] ([prod_id], [prod_desc], [Remark]) VALUES (7, 'ball', 'Green')

Suthish Nair · Answer

I think SQL Server Fun is back.. more members participating :) ..
My question was can we do like this:
INSERT [dbo].[Table1] ([id], [name], [date]) VALUES ( 1 , 'a', 'b')
INSERT [dbo].[Table1] ([id], [name], [date]) VALUES ( 1 , 'a','b')
INSERT [dbo].[Table1] ([id], [name], [date]) VALUES ( 1 , 'a1', 'b2')
INSERT [dbo].[Table1] ([id], [name], [date]) VALUES ( 1 , 'a2, 'b3')
INSERT [dbo].[Table1] ([id], [name], [date]) VALUES ( 1 , '4a', 'b4')
So its yes..
I will post more questions..

Vilas Gite · Answer

hi suthish ji..
yes! it is possible
using reseed :  dbcc checkident (rd, reseed, 4)
1 office stationary admin 2 Pen staff 3 Pencil staff 4 Pencil staff 5 Pen staff 6 Pencil staff 7 color pen staff 8 color pen staff 31 pins staff 5 cell admin
---------------------------------------------------------------------
If this reply solve your post…then " MARK AS ANSWER "! :)

Vilas Gite · Answer

after executing SQL!
Incrementation on Identity coloumn automaticly done without duplicating values

Vilas Gite · Answer

hi suthish ji..
here, i am trying with rd table
"set identity_insert rd on"
output after i am trying
1 office stationary admin 2 Pen staff 3 Pencil staff NULL Pencil staff NULL Pen staff NULL Pencil staff 7 color pen staff NULL color pen staff

Suthish Nair · Answer

sorry, i edited the post.. its not primary key column. Its a Identity column and i want to insert duplicate values.

Abhimanyu K Vatsa · Answer

if really that has Primary Key column then, it is not possible. Sometimes inserting many records once contain many new and duplicate values. Maybe to prevent this, we can take the advantage of 'distinct' keyword.
What you say? Is this really possible?

1	office stationary	admin
2	Pen	staff
3	Pencil	staff
4	Pencil	staff
5	Pen	staff
6	Pencil	staff
7	color pen	staff
8	color pen	staff
31	pins	staff
5	cell	admin