CODEWITHSUNDEEP
sqlsql-serverincrement
AlexanderK
AlexanderK

Reputation: 365

Increment non-primary key fieild within INSERT query

I'm trying to copy rows from old company to the new one.

SET IDENTITY_INSERT [SomeDB].[dbo].[SomeTable] ON;

INSERT INTO [SomeDB].[dbo].[SomeTable] 
       ([HRCo]
      ,[Code]
      ,[Type]
      ,[Description]
      ,[SafetyYN]
      ,[Notes]
      ,[UniqueAttchID]
      ,[CertPeriod]   
      ,[KeyID]
      ,[PTOTypeYN]
      ,[PRLeaveCode]) 
SELECT '6',
       SomeTable.Code
      ,SomeTable.Type
      ,SomeTable.Description
      ,SomeTable.SafetyYN
      ,SomeTable.Notes
      ,SomeTable.UniqueAttchID
      ,SomeTable.CertPeriod    
      ,[KeyID]      
      ,SomeTable.PTOTypeYN
      ,SomeTable.PRLeaveCode
      FROM [SomeDB].[dbo].[SomeTable] 
WHERE HRCo = '11'

SET IDENTITY_INSERT [SomeDB].[dbo].[SomeTable] OFF;

What i get as result is an error:

Cannot insert duplicate key row in object 'dbo.SomeTable' with unique index 'biKeyID'. The duplicate key value is (1377).

Is there a way to use increment for this column? (It's not a primary key column) or maybe there's another way to copy multiple records?

Upvotes: 0

Views: 226

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

You said in the comments that primary key is on ([HRCo], [Code], [Type]) and IDENTITY is KeyID. This information should be in the question. It is important for understanding what is going on.

You are setting the HRCo to 6 for your copy, so I assume that this guarantees that new primary keys are unique.

As for the KeyID, the IDENTITY column will get its (new, incremented) values automatically during INSERT, so you should not include this column in the INSERT statement. This is what IDENTITY does. IDENTITY assigns new sequential values to new rows automatically. And you should not SET IDENTITY_INSERT here. Let IDENTITY do what it does.

INSERT INTO [SomeDB].[dbo].[SomeTable] 
       ([HRCo]
      ,[Code]
      ,[Type]
      ,[Description]
      ,[SafetyYN]
      ,[Notes]
      ,[UniqueAttchID]
      ,[CertPeriod]   
      --,[KeyID]
      ,[PTOTypeYN]
      ,[PRLeaveCode]) 
SELECT '6',
       SomeTable.Code
      ,SomeTable.Type
      ,SomeTable.Description
      ,SomeTable.SafetyYN
      ,SomeTable.Notes
      ,SomeTable.UniqueAttchID
      ,SomeTable.CertPeriod    
      --,[KeyID]      
      ,SomeTable.PTOTypeYN
      ,SomeTable.PRLeaveCode
FROM [SomeDB].[dbo].[SomeTable] 
WHERE HRCo = '11'

Note, that I removed the KeyID column from both INSERT and SELECT statements.

Upvotes: 2

Fabio Romero
Fabio Romero

Reputation: 27

If you are using

SET IDENTITY_INSERT [SomeDB].[dbo].[SomeTable] ON;

You don't have to set the PK value. For example, if your PK is KeyID, remove this column from your INSERT.

It would be something like this:

SET IDENTITY_INSERT [SomeDB].[dbo].[SomeTable] ON;

INSERT INTO [SomeDB].[dbo].[SomeTable] 
       ([HRCo]
      ,[Code]
      ,[Type]
      ,[Description]
      ,[SafetyYN]
      ,[Notes]
      ,[UniqueAttchID]
      ,[CertPeriod]   
      ,[PTOTypeYN]
      ,[PRLeaveCode]) 
SELECT '6',
       SomeTable.Code
      ,SomeTable.Type
      ,SomeTable.Description
      ,SomeTable.SafetyYN
      ,SomeTable.Notes
      ,SomeTable.UniqueAttchID
      ,SomeTable.CertPeriod     
      ,SomeTable.PTOTypeYN
      ,SomeTable.PRLeaveCode
      FROM [SomeDB].[dbo].[SomeTable] 
WHERE HRCo = '11'

Upvotes: 1

Related Questions