SQL Server Insert Into Query with Not Exists

[skeletor]

I am having trouble with the code for an SQL Server Insert Into Query. The result gives an error message that there has been a "violation of primary key restraint". I have researched here and found that I need an If Not Exists statement within the code, but I haven't been able to get that code correct. I want to append records from one table to another. The example would be an 'EmployeeTable' Name, ReasonOff, Shiftdate, PositionID. The primary keys are PositionId and Shiftdate. I want to append records from that table to a TimeCard table when the ReasonOff equals 'S'. The code I used is:

Insert into [EmployeeTable] (Name, ReasonOff, Shiftdate, PositionID)
Select TimeCard.Name, TimeCard.ReasonOff, TimeCard.Shiftdate, TimeCard.PositionID
From TimeCard
Where (EmployeeTable.ReasonOff) = 'S';

Can you please help me with the If Not Exists code.

 

[gmmastros]

I've never been a big fan of the Not Exists format. I understand that Not Exists is supposed to be slightly faster, but I never really liked the syntax. Instead, I would left join the tables and only insert those rows that don't exist in the other table. Like this:

Insert 
into   [EmployeeTable] (Name, ReasonOff, Shiftdate, PositionID)
Select TimeCard.Name, TimeCard.ReasonOff, TimeCard.Shiftdate, TimeCard.PositionID
From   TimeCard
       Left Join EmployeeTable
         On TimeCard.PositionId = EmployeeTable.PositionId
         And TimeCard.ShiftDate = EmployeeTable.ShiftDate
Where  EmployeeTable.PositionId Is NULL 
       And [!]TimeCard[/!].ReasonOff = 'S';

By the way, you originally had EmployeeTable.ReasonOff, but I assume you really wanted TimeCard.ReasonOff instead.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[imex]

Try:

Insert into TimeCard (Name, ReasonOff, Shiftdate, PositionID)
    Select e.Name, e.ReasonOff, e.Shiftdate, e.PositionID
    From EmployeeTable as e
    Where e.ReasonOff = 'S' and
          not exists (select 1 from TimeCard as t
                      where t.PositionID = e.PositionID and
                            t.Shiftdate = e.Shiftdate)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[skeletor]

Thanks very much to both respondees. I was able to use the code submitted by imex. That worked perfectly and is much appreciated.