SQL Insert IF不存在循环 [英] SQL Insert IF not exists loop

问题描述

嗨朋友们，



我对此查询感到困惑。

我想在表中插入4条记录我正在使用以下查询

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

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

适用于该场景

如果表中不存在所有4条记录，则输入4条记录cessfully。

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.

预期：我想插入第4条记录，即使表格中存在第一/第二/第三个ID。这意味着我想插入一条新记录，留下以前存在的记录

我试过这个

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

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

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

但这也失败了:(



示例输入：



我有4条记录，如6589,6857,1220,4500

现在我想通过check插入WaitingStatus表记录是否存在。

工作方案：

如果Waitingschedules表没有全部4条记录，那么它的所有成功插入。



失败的情况：

如果Waitingschedules表有6589（第一条记录），那么剩余的记录甚至不会被插入记录不存在。



请帮我解决这个问题。



谢谢，

RK

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

推荐答案

看看例子：

Have a look at example:

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

结果：

1.query - 插入之前

Results:
1.query - before insert

ID
6589

2。查询 - 插入后

2.query - after insert

ID
6589
6857
1220
4500

使用CTE和ID列表作为varchar变量：

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

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

看看OriginalGriff的提示：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

我进一步优化了查询以插入不存在的记录，



感谢Maciej Los for NOT IN概念（想知道为什么我没想到; - ））

这是我使用的查询

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

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

问候，

RK

Regards,
RK

这篇关于SQL Insert IF不存在循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！