INSERT INTO (Only when record doesn't exist)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT INTO (Only when record doesn't exist)

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-23 : 10:39:00

Hello,

I have the following code which works great :



INSERT INTO tbl_Holidays(EmployeeNumber,HolidayDate,NumberOfHours)
SELECT EmployeeNo, @BankHolidayDateConverted, dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate)

However, how can I make it so that the code only runs if a matching record doesn't already exist ?

I thought it would be something like :


IF NOT EXISTS(SELECT * FROM tbl_Holidays WHERE EmployeeNumber = @EmployeeNo AND HolidayDate = @BankHolidayDateConverted)
INSERT INTO tbl_Holidays(EmployeeNumber, HolidayDate, NumberOfHours)
SELECT @EmployeeNo, @BankHolidayDateConverted, dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate)
ELSE

But that seems to get me stuck in a loop.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-23 : 10:41:26

INSERT INTO tbl_Holidays(EmployeeNumber,HolidayDate,NumberOfHours)
SELECT @EmployeeNo, @BankHolidayDateConverted, dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate)
where not exists (select * from tbl_Holidays where EmployeeNumber = @EmployeeNo AND HolidayDate = @BankHolidayDateConverted)

or
if not exists (select * from tbl_Holidays where EmployeeNumber = @EmployeeNo AND HolidayDate = @BankHolidayDateConverted)
INSERT INTO tbl_Holidays(EmployeeNumber,HolidayDate,NumberOfHours)
SELECT @EmployeeNo, @BankHolidayDateConverted, dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 10:42:10

Is this a part of your cursor?

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-23 : 10:47:19

It is part of the cursor yes.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-23 : 10:51:20

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources