CODEWITHSUNDEEP
sqlsql-server
Godwin
Godwin

Reputation: 54

Violation of Primary Key Constraint, Inserting into two tables based on GUID

DECLARE @NewSessionIDTable TABLE (NewSessionID UNIQUEIDENTIFIER)  


INSERT INTO TrainingSession
( 
    TrainingID
   ,TrainingSessionStatusID
   ,TrainingSessionDesc
   ,TrainingSessionDate
   ,TrainingSessionTime
   ,TrainingSessionDuration
   ,TrainingSessionLocation
   ,TrainingSessionTrainer
   ,TrainingSessionNotes
   ,LastModified
   ,RevisionNum
) 

OUTPUT INSERTED.TrainingSessionID INTO @NewSessionIDTable  

SELECT 
        training.TrainingID
      , 0
      , '2017 Training'
      , null
      , null
      , null
      , 'Online'
      , null
      , null
      , GETDATE()
      , 1

FROM  TrainingSession, dbo.AnnualData illumivuData

WHERE  training.TrainingTypeID = 26
       AND illumivuData.Status = 'Users Not Started' 
       AND illumivuData.DepartmentIDTEXT IN ( SELECT DepartmentID from Training.Department) 
       AND illumivuData.FirmEmployeeIDTEXT IN (SELECT EmployeeID from Training.Employees)



INSERT INTO 
 -- Another Table the outputted GUID that was generated above.

I need to insert data into two tables, with the insertion into the second table being dependent on a GUID genereated after inserting a new row into the first table.

I have succesfully gotten through the first part, as e.NewPk is the primary key generated by an earlier insert statement (held in a table variable). The issue I am running into now is that when executing this second insert statement, I recieve a violation of primary key constraint error.

In order to check that the insert wasn't actually returning duplicates, I ran this exact same insert statement into a temporary table. No duplicates.

But when I run it in tandem with the earlier insert statement, i receive the error.

Any idea on what to do here? I definitely think I'm approaching this wrong.

Upvotes: 0

Views: 591

Answers (2)

Godwin
Godwin

Reputation: 54

The solution to this problem was to declare a temp table upfront with all the information needed and simply generating a GUID with NEWID() as opposed to allowing the tables to generate them by themselves. 

CREATE TABLE #TempOnline
( 
  TrainingSessionID uniqueidentifier, 
  TrainingStatus nvarchar(70), 
  FirmEmployeeID nvarchar(25), 
  DepartmentID nvarchar(25), 
  StartDate datetime, 
  SectionDate datetime,

) 

INSERT INTO #TempOnline

select NEWID(), 'Users Not Started' , '1535' , '0100' , '2017-04-17 00:00:00' , NULL

This is what it looks like, except with many more union all select's after the initial one.

After this I go on to insert data into TrainingSession and TrainingEmployee just straight from this TempTable.

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

start with changing your "joins" to this:

FROM  ComplianceTraining.Training a
INNER JOIN dbo.AnnualOnline2017Data b
  ON ??? scalar ???
INNER JOIN ComplianceTraining.EdsDepartment c
  ON c.DepartmentID = b.DepartmentIDTEXT
INNER JOIN ComplianceTraining.EdsGeneralEmployee d
  ON d.EdsGeneralEmployee = b.FirmEmployeeIDTEXT 
INNER JOIN @NewPKTable e
  ON ???
WHERE 
     a.TrainingTypeID = 26
AND  b.Status = 'Users Not Started'

Upvotes: 2

Related Questions