Sql Insert with same id but different values in another column

Question

Hi All,

I need the requirements of inserting multiple rows of same ID

My Table Data:

ID StoreID
1001 243
1002 243
1003 243
1004 243

Required Output Should be,

ID StoreID
1001 243
1001 250
1002 243
1002 250
1003 243
1003 250

Please help me to achieve this output.

Thanks.

Answer 1

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.

Answer 2

If you mean to copy data of on StoreID to an other StoreID, then this should work for you

INSERT INTO yourTable
   (ID, StoreID)
SELECT ID, 250 AS StoreID
FROM yourTable
WHERE StoreID = 243

Answer 3

Do you get the error, because data with StoreID = 250 already exists? Then exclude existing data:

 INSERT INTO yourTable
    (ID, StoreID)
 SELECT ID, 250 AS StoreID
 FROM yourTable AS MAIN
 WHERE StoreID = 243
       AND NOT EXISTS (SELECT 1 FROM yourTable AS SUB WHERE SUB.StoreID = 250 AND SUB.ID = MAIN.ID)