仅当表不存在记录时才插入记录
问题说明
我想知道是否有一种方法可以在表中不包含该记录的情况下将记录插入到表中?
I'm wondering if there is a way to insert a record into a table only if the table does not already contain that record?
是否有可以执行此操作的查询,或者我是否需要一个存储过程?
Is there a query that will do this, or will I need a stored procedure?
正确答案
你不说是什么版本的 SQL Server.如果 SQL Server 2008 你可以使用 MERGE
You don't say what version of SQL Server. If SQL Server 2008 you can use MERGE
注意:通常将 Merge 用于 Upsert,这是我最初认为问题所问的问题,但它在没有 WHEN MATCHED
子句的情况下是有效的,只有一个 WHEN NOT MATCHED
子句也适用于这种情况.示例用法.
NB: It is usual to use Merge for an Upsert which is what I originally thought the question was asking but it is valid without the WHEN MATCHED
clause and just with a WHEN NOT MATCHED
clause so does work for this case also. Example Usage.
CREATE TABLE #A(
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[C] [varchar](200) NOT NULL)
MERGE #A AS target
USING (SELECT 3, 'C') AS source (id, C)
ON (target.id = source.id)
/*Uncomment for Upsert Semantics
WHEN MATCHED THEN
UPDATE SET C = source.C */
WHEN NOT MATCHED THEN
INSERT (id, C)
VALUES (source.id, source.C);
就执行成本而言,当要完成插入时,两者看起来大致相等...
In terms of execution costs the two look roughly equal when an Insert is to be done...
但是在第二次运行时没有插入操作时,马修的答案看起来成本更低.我不确定是否有办法改善这一点.
but on the second run when there is no insert to be done Matthew's answer looks lower cost. I'm not sure if there is a way of improving this.
测试脚本
select *
into #testtable
from master.dbo.spt_values
CREATE UNIQUE CLUSTERED INDEX [ix] ON #testtable([type] ASC,[number] ASC,[name] ASC)
declare @name nvarchar(35)= 'zzz'
declare @number int = 50
declare @type nchar(3) = 'A'
declare @low int
declare @high int
declare @status int = 0;
MERGE #testtable AS target
USING (SELECT @name, @number, @type, @low, @high, @status) AS source (name, number, [type], low, high, [status])
ON (target.[type] = source.[type] AND target.[number] = source.[number] and target.[name] = source.[name] )
WHEN NOT MATCHED THEN
INSERT (name, number, [type], low, high, [status])
VALUES (source.name, source.number, source.[type], source.low, source.high, source.[status]);
set @name = 'yyy'
IF NOT EXISTS
(SELECT *
FROM #testtable
WHERE [type] = @type AND [number] = @number and name = @name)
BEGIN
INSERT INTO #testtable
(name, number, [type], low, high, [status])
VALUES (@name, @number, @type, @low, @high, @status);
END
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /reply/detail/tangibjif
-
在C#代码查找所有递归调用
-
Git Cherry-pick 和合并工作流
-
React Native TextInput blur消耗TouchableHighlight按事件
-
替换"#"," $","%","&放大器;"和" _"和" \\#"," \\ $"," \\%QUOT;," \\&安培;"和" \\ _"
-
pandas :不同的功能应用于不同的列
-
DOMDocument :: validate()问题
-
YouTube API 不能在 iOS (iPhone/iPad) 工作,但在桌面浏览器工作正常?
it1352 07-30 -
保持在后台运行的 iPhone 应用程序完全可操作
it1352 07-25 -
iPhone,一张图像叠加到另一张图像上以创建要保存的新图像?(水印)
it1352 07-17 -
使用 iPhone 进行移动设备管理
it1352 07-23 -
在android同时打开手电筒和前置摄像头
it1352 09-28 -
扫描 NFC 标签时是否可以启动应用程序?
it1352 08-02 -
检查邮件是否发送成功
it1352 07-25 -
Android微调工具-删除当前选择
it1352 06-20 -
希伯来语的空格句子标记化错误
it1352 06-22 -
Android App 和三星 Galaxy S4 不兼容
it1352 07-20