question

SuganyaNagaraj-9147 avatar image
0 Votes"
SuganyaNagaraj-9147 asked MelissaMa-msft commented

Sql Insert with same id but different values in another column

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.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SuganyaNagaraj-9147,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Viorel-1 commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How to avoid this error: violation of primary key constraint. cannot insert duplicate key in sql server?

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 SuganyaNagaraj-9147 ·

Maybe the ID column is a primary key and does not allow duplicates?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SuganyaNagaraj-9147,

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:
83410-primary.png

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.




primary.png (7.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.