SQL Insert IF not exists loop

Question

0.00/5 (No votes)

See more:

Hi Friends,

I am stuck up with this query.
I want to insert 4 records in to the table for that am using the below query

SQL

IF NOT EXISTS(SELECT WS.ScheduleID FROM WaitingSchedules WS,@waitingSchedules_temp WST WHERE WST.ScheduleID = WS.ScheduleID)
            INSERT INTO WaitingSchedules SELECT ScheduleID,AppointmentStatus,InDt,OutDt,HasUpdated FROM @waitingSchedules_temp

Works fine for the scenario
If all the 4 records are not exist in the table then 4 records are inserted successfully.

Fails in the below scenario<br />
If first records is exist in table then remaining first and remaining 3 records are failed to insert in table.

Expected: I want to insert 4th record even if first/second/third id is exist in the table.Which means I want to insert a new record leaving previously existing records

I tried this

SQL

WHILE (@Count < @RowCount)
BEGIN

    IF NOT EXISTS (SELECT WS.ScheduleID FROM WaitingSchedules WS,@waitingSchedules_temp WST WHERE WST.ScheduleID = WS.ScheduleID)
    INSERT INTO WaitingSchedules SELECT ScheduleID,AppointmentStatus,InDt,OutDt,HasUpdated FROM @waitingSchedules_temp

    SET @Count = @Count + 1;
END

And also this

SQL

WHILE (NOT EXISTS(SELECT WS.ScheduleID FROM WaitingSchedules WS,@waitingSchedules_temp WST WHERE WST.ScheduleID = WS.ScheduleID))
            INSERT INTO WaitingSchedules SELECT ScheduleID,AppointmentStatus,InDt,OutDt,HasUpdated FROM @waitingSchedules_temp

But this also failed :(

Sample input:

I have 4 records like 6589, 6857, 1220, 4500
Now I want to insert into WaitingStatus Table by checking if the records are pre exist.
Working scenario:
If Waitingschedules table doesnt have all the 4 records then its inserting all successfully.

Failing scenario:
If Waitingschedules table has 6589(first record) then remaining records are not inserting even remaining records are not exist.

Please help me on this issue.

Thanks,
RK

Posted 19-Dec-13 2:03am

♥…ЯҠ…♥

Updated 19-Dec-13 2:34am

v5

Add a Solution

Comments

Maciej Los 19-Dec-13 8:28am

RK, please provide sample data and expected outtput. It would be easier to understand.

♥…ЯҠ…♥ 19-Dec-13 8:31am

Sure Maciej

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-19T02:43:00

Have a look at example:

SQL

DECLARE @ws TABLE(ID INT)

INSERT INTO @ws (ID)
VALUES(6589)

SELECT ID
FROM @ws

INSERT INTO @ws (ID)
SELECT ID
FROM (
    SELECT 6589 AS ID UNION ALL
    SELECT 6857 AS ID UNION ALL
    SELECT 1220 AS ID UNION ALL
    SELECT 4500 AS ID
) AS T
WHERE ID NOT IN(SELECT ID FROM @ws)

SELECT ID
FROM @ws

Results:
1.query - before insert

ID
6589

2.query - after insert

Using CTE and list of ID's as a varchar variable:

SQL

DECLARE @ValuesToInsert VARCHAR(300) = '6589,6857,1220,4500'

--use CTE to split values
;WITH VTI AS 
(
	SELECT CONVERT(INT, LEFT(@ValuesToInsert, CHARINDEX(',', @ValuesToInsert)-1)) AS TID, RIGHT(@ValuesToInsert, LEN(@ValuesToInsert) - CHARINDEX(',', @ValuesToInsert)) AS Remainder
	WHERE CHARINDEX(',', @ValuesToInsert)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS TID, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM VTI
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT,Remainder) AS TID, NULL AS Remainder
	FROM VTI
	WHERE CHARINDEX(',', Remainder)=0
)
INSERT INTO @ws (ID)
SELECT ID 
FROM 
	(
	SELECT TID AS ID
	FROM VTI
	) AS T
WHERE ID NOT IN (SELECT ID FROM @ws)

SELECT ID
FROM @ws

Have a look at OriginalGriff's tip: Using comma separated value parameter strings in SQL IN clauses[^]

♥…ЯҠ…♥ · Accepted Answer · 2013-12-19T03:58:00

Hi,

I optimized the query further to insert not existed records,

Thanks Maciej Los for NOT IN concept(Wondering why I didnt think that ;-))
This is the query that I used

SQL

INSERT INTO WaitingSchedules SELECT ScheduleID,AppointmentStatus,InDt,OutDt,HasUpdated
            FROM @waitingSchedules_temp AS tmp
            WHERE (tmp.ScheduleID NOT IN (SELECT ScheduleID FROM WaitingSchedules))

Regards,
RK

SQL Insert IF not exists loop

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0