If Exists Insert Issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

If Exists Insert Issue

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-22 : 10:51:38

Hi,

I have a simple stored procedure that checks for records if they exist in a table. If they exist,
I really don"t need to do anything with them, but if they do NOT exist, insert them into the table.
When I run this stored proc, it displays the select statements I have but for some reason,
it is NOT inserting a couple of records that exist in JB_Items table but not in Items table.
Here are the two records that I was wanted to be inserted into the Items table -

Item_Id Item_Name SS_Item_ID SSRoomID Vendor_Id Vendor_Name Catalog_No Pac_Type Units_Per_Pac Cost_Per_Pac
-------------------- --------------------------------- ----------- ----------- ----------------- -------------- -------------- -------------------- ------------- ---------------------
CATHET.....5348 Multi-pack JL4, JR4 Str Pig 6Fr NULL NULL VENDOR.......26 SciMed/BSci 08641-300 box 5 127.50
TEST jb_ITEM 0 0 VENDOR TEST VENDOR TEST_CATALOG BOX 1 1.00

(2 row(s) affected)

Any help would be greatly appreciated. Thanks.

USE [Apollo_Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ItemsUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ItemsUpdate]
GO
CREATE PROC dbo.ItemsUpdate

AS
SET NOCOUNT ON

--Begin Tran

Select * from JB_Items

BEGIN
-- Check Apollo_Prod Demographics If Record Exists
If Exists (Select * From Items)
Select * from JB_Items
Where Item_ID NOT IN (select Item_ID from Items)
Else
Begin Try
-- Insert NEW Apollo_Prod Demographics Record From Laptop
Insert Into Items
Select * from JB_Items
Where Item_ID NOT IN (select Item_ID from Items)
End Try

Begin Catch
If ERROR_NUMBER() <> 2627 AND ERROR_NUMBER() <> 0
Begin
RaisError('Cannot add or change record. Referential integrity rules require a related record in table %s', 16, 1, 'Event_Cath')
-- Rollback
Select ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
Return
End
End Catch;
END

--COMMIT

--IF @@ERROR <> 0
--BEGIN
-- ROLLBACK
--RETURN @@ERROR
--END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-22 : 11:02:09

As far as "select * from Items" returns anything your ELSE is never reached.

Do this:
Insert Into Items
Select * from JB_Items
--Where Item_ID NOT IN (select Item_ID from Items)
where not exists(select * from Items where Items.Item_ID = JB_Items.Item_ID)

Edit: without IF ... ELSE ...

No, you're never too old to Yak'n'Roll if you're too young to die.

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-22 : 11:50:48

Thanks for the quick reply. I can do this simple insert select statement but I need it in a stored proc with the If Else construct to check for existence because I may need to do an update if it does exist later on. I will also be building on this stored proc to included several other checks on different tables.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 12:11:44

First I'd suggest you use NOT EXISTS or LEFT OUTER JOIN instead of IN, preferably a join.

But, more importantly, why do any records get missed at all? Are the data types exactly the same? Are you using NOLOCKs or setting the ISOLATION LEVEL to READ UNCOMMITTED? Can you reproduce the records not getting inserted using table variables as a test?

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-22 : 14:50:02

I'm working with a different set of tables - one being a copy of the other table and here is my IF statement -

If Not Exists (Select * From JB_InvSupplyRooms)
Begin Try
-- Insert NEW Apollo_Prod Demographics Record From Laptop
SET IDENTITY_INSERT JB_InvSupplyRooms ON

Insert Into JB_InvSupplyRooms ([SSRoomID], [Room_Name], [Department], [Sequence])
Select SSRoomID, Room_Name, Department, Sequence from InvSupplyRooms
Where Not Exists (select * from JB_InvSupplyRooms)

SET IDENTITY_INSERT JB_InvSupplyRooms OFF
End Try

Table InvsuppyRooms has the ff records:

SSRoomID Room_Name Department Sequence
----------- ----------------- -------------------------------------------------- --------
2 Cath_Supply Cath Lab 0
5 IR_East Cath Lab 1
6 IR_West Cath Lab 2
7 R_Pine Cath Lab 3
8 Plaza Cath Lab 4

(5 row(s) affected)

My stored proc worked the first time so it populated the JB_InvSupplyRooms with the 5 records from InvSupplyrooms. So I deleted a couple of records from JB_InvSupplyRooms but when I ran my stored proc again. It did not add the 2 records I deleted back into JB_InvSupplyRooms. Also, if I run the ff statement :

Select *
from InvSupplyRooms
where room_name not in (select room_name from JB_InvSupplyRooms)

I get this result -

SSRoomID Room_Name Department Sequence
----------- ---------------------- -------------------------------------------------- --------
2 Cath_Supply Cath Lab 0
7 R_Pine Cath Lab 3

(2 row(s) affected)

But when I run this -

Select *
from InvSupplyRooms
Where Not Exists (select * from JB_InvSupplyRooms)

I get nothing. Pls help

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 15:29:37

Well the reson it didn't work is becuase you are doing the NOT EXISTS on SELECT *. The table has data in it so of course you are not going to insert anything. What you seem to be doing is simple delta logic.

I see that you have an Indenity column, but then you are using the Room_Name to check for the existing records. So, I'm still confused. Maybe this will help (notice how I supplied the DDL and DML in a way that you can run the script without having to typye it all in yourself... that is how you present your data)

-- Set up data
DECLARE @InvSupplyRooms TABLE
(
	SSRoomID INT,
	Room_Name VARCHAR(20),
	Department VARCHAR(20),
	Sequence INT
)

INSERT @InvSupplyRooms
SELECT 2, 'Cath_Supply', 'Cath Lab', 0
UNION ALL SELECT 5, 'IR_East', 'Cath Lab', 1
UNION ALL SELECT 6, 'IR_West', 'Cath Lab', 2
UNION ALL SELECT 7, 'R_Pine', 'Cath Lab', 3
UNION ALL SELECT 8, 'Plaza', 'Cath Lab', 4

DECLARE @JB_InvSupplyRooms TABLE
(
	SSRoomID INT,
	Room_Name VARCHAR(20),
	Department VARCHAR(20),
	Sequence INT
)

-- Load the table with missing records
INSERT
	@JB_InvSupplyRooms
SELECT
	Room.*
FROM
	@InvSupplyRooms AS Room
LEFT OUTER JOIN
	@JB_InvSupplyRooms AS JB
	ON Room.SSRoomID = JB.SSRoomID
WHERE
	JB.SSRoomID IS NULL
	
SELECT *
FROM @JB_InvSupplyRooms

-- Delete some of those records
DELETE @JB_InvSupplyRooms
WHERE SSRoomID IN (5, 7, 8)

SELECT *
FROM @JB_InvSupplyRooms

-- Add in the missing recods.
INSERT
	@JB_InvSupplyRooms
SELECT
	Room.*
FROM
	@InvSupplyRooms AS Room
LEFT OUTER JOIN
	@JB_InvSupplyRooms AS JB
	ON Room.SSRoomID = JB.SSRoomID
WHERE
	JB.SSRoomID IS NULL
	
-- And all the records are there again!
SELECT *
FROM @JB_InvSupplyRooms

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-22 : 15:50:32

quote:
Originally posted by josh2009

Hi,

I have a simple stored procedure that checks for records if they exist in a table. If they exist,
I really don"t need to do anything with them, but if they do NOT exist, insert them into the table.

Where are we getting the "Feeds" from in the first place?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-22 : 16:38:41

Thank you. Well, the reason why I'm using an IF construct is because I have to check first if the tables are not in sync. If there are new records in one table and are not in the other table, then I insert which is why I am using the EXISTS which is obviously flawed the way I'm using it. So, how can I use the IF with EXISTS so I can check for missing records in one table. Thanks

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-07-23 : 09:18:02

Thanks for your Lamprey. I was able to resolve the issue using a select stmt with left outer join and checking for nulls and then used @@rowcount to check for any records returned. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources