I want to insert into my Files-Favorites table only if the values I'm trying to pass isn't in there already.
I tried:
INSERT INTO [Files-Favorites](fileID,auditorID)
VALUES ('1', '34')
WHERE (fileID != '1'
AND auditorID != '34')
This doesn't work. I'm trying not to INSERT duplicate values. How do I pull this off? This is for a Microsoft SQL Server 2005.
Try using if not exists
IF NOT EXISTS(SELECT * FROM [Files-Favorites] WHERE fileID = '1' AND auditorID = '34')
BEGIN
INSERT INTO [Files-Favorites](fileID, auditorID)
VALUES('1', '34')
END
I would use a composite (multi-column) key rather than checking on each insert
ALTER TABLE [Files-Favorites] ADD CONSTRAINT unique_1 UNIQUE(fileID,auditorID)
We can use EXISTS with sub query to check the data existence and insert accordingly:
INSERT INTO [Files-Favorites](fileID,auditorID)
SELECT fileID, auditorID FROM (
SELECT '1' fileID,'34' auditorID) t
WHERE NOT EXISTS(SELECT tm.fileID FROM [Files-Favorites] tm
WHERE tm.fileID = t.fileID AND tm.auditorID = t.auditorID)
