Hi @Suganya Nagaraj ,
Welcome to Microsoft Q&A!
Please refer below example:
create table tableS
(
ID int primary key,
StoreID int
)
insert into tableS values
(1001,243),
(1002,243),
(1003,243),
(1004,243)
select * from tableS
INSERT INTO tableS
(ID, StoreID)
SELECT ID, 250 AS StoreID
FROM tableS
WHERE StoreID = 243
Then we received below error message:
The primary key (and other candidate keys) prevent inserting duplicate values. So your requirement could not be achieved.
But if you insist on inserting multiple rows of same ID, you have to remove the primary key or add/change another column as primary key.
Please refer below which could remove the primary key.
-- Return the name of primary key.
SELECT name
FROM sys.key_constraints
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'tableS';
GO
-- Delete the primary key constraint.
ALTER TABLE tableS
DROP CONSTRAINT PK__tableS__3214EC2768298751;
GO
Then we could insert the values sucessfully.
INSERT INTO tableS
(ID, StoreID)
SELECT ID, 250 AS StoreID
FROM tableS
WHERE StoreID = 243
select * from tableS order by ID,StoreID
Output:
ID StoreID
1001 243
1001 250
1002 243
1002 250
1003 243
1003 250
1004 243
1004 250
In addition, you could find out which column has been set as primary key as below:
SELECT C.Name
FROM sys.index_Columns IC
INNER JOIN sys.indexes I
ON IC.index_id = I.index_id
AND IC.object_id = I.object_id
INNER JOIN sys.Columns C
ON C.object_id = I.object_id
AND C.column_id = IC.column_id
WHERE OBJECT_NAME(IC.object_id) = 'tableS' --your table name
AND I.is_primary_key = 1
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.