SQL Server 在不存在的地方插入值

lndjwyie  于 2022-11-21  发布在  其他
关注(0)|答案(6)|浏览(139)

好的，我正在尝试改进我的asp数据输入页面，以确保进入我的数据表的条目是唯一的。
在这个表中，我有SoftwareName和SoftwareType。我正在尝试获取它，这样如果入口页面发送一个插入查询，其中的参数与表中的参数匹配（因此标题和类型相同），则会抛出一个错误，并且不会输入数据。
大概是这样的：

INSERT INTO tblSoftwareTitles( 
            SoftwareName,  
            SoftwareSystemType) 
            VALUES(@SoftwareName,@SoftwareType) 
            WHERE NOT EXISTS (SELECT SoftwareName 
            FROM tblSoftwareTitles 
            WHERE Softwarename = @SoftwareName 
            AND SoftwareType = @Softwaretype)

因此，这种语法非常适合从一个表中选择列到另一个表中，而不需要输入重复的列，但似乎不想使用参数化插入查询。有人能帮我解决这个问题吗？
编辑：
下面是我在ASP插入方法中使用的代码

private void ExecuteInsert(string name, string type)
{
    //Creates a new connection using the HWM string
    using (SqlConnection HWM = new SqlConnection(GetConnectionStringHWM()))
    {
        //Creates a sql string with parameters
        string sql = " INSERT INTO tblSoftwareTitles( "
                   + " SoftwareName, " 
                   + " SoftwareSystemType) "
                   + " SELECT "
                   + " @SoftwareName, "
                   + " @SoftwareType "
                   + " WHERE   NOT EXISTS  "
                   + " ( SELECT  1 "
                   + " FROM tblSoftwareTitles "
                   + " WHERE Softwarename = @SoftwareName "
                   + " AND SoftwareSystemType = @Softwaretype); ";         

        //Opens the connection
        HWM.Open();
        try
        {
            //Creates a Sql command
            using (SqlCommand addSoftware = new SqlCommand{
                CommandType = CommandType.Text,
                Connection = HWM,
                CommandTimeout = 300,
                CommandText = sql})
            {
                //adds parameters to the Sql command
                addSoftware.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = name;
                addSoftware.Parameters.Add("@SoftwareType", SqlDbType.Int).Value = type;
                //Executes the Sql
                addSoftware.ExecuteNonQuery();
            }
            Alert.Show("Software title saved!");
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }

    }
}
sql-server

6条答案

按热度按时间
ghhkc1vu

ghhkc1vu1#

您可以使用IF陈述式来执行此作业：

IF NOT EXISTS 
    (   SELECT  1
        FROM    tblSoftwareTitles 
        WHERE   Softwarename = @SoftwareName 
        AND     SoftwareSystemType = @Softwaretype
    )
    BEGIN
        INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
        VALUES (@SoftwareName, @SoftwareType) 
    END;

您可以在没有IF的情况下使用SELECT来执行此操作

INSERT  tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
SELECT  @SoftwareName,@SoftwareType
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    tblSoftwareTitles 
            WHERE   Softwarename = @SoftwareName 
            AND     SoftwareSystemType = @Softwaretype
        );

这两种方法都容易受到race condition的影响，因此，虽然我仍然使用上述方法之一进行插入，但您可以使用唯一约束来保护重复插入：

CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType
    ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);

SQL-Fiddle示例

附录
在SQL Server 2008或更高版本中，可以将MERGE与HOLDLOCK一起使用，以消除出现争用条件的可能性（争用条件仍然不能替代唯一约束）。

MERGE tblSoftwareTitles WITH (HOLDLOCK) AS t
USING (VALUES (@SoftwareName, @SoftwareType)) AS s (SoftwareName, SoftwareSystemType) 
    ON s.Softwarename = t.SoftwareName 
    AND s.SoftwareSystemType = t.SoftwareSystemType
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (SoftwareName, SoftwareSystemType) 
    VALUES (s.SoftwareName, s.SoftwareSystemType);

SQL Fiddle上的合并示例

赞(0）分享  回复(0）举报  2022-11-21
y4ekin9u

y4ekin9u2#

    • 这 不是 答案 。 * * 我 只 想 说明 IF NOT EXISTS(...) INSERT 方法 是 不 安全 的 。 您 必须 先 执行 Session #1 ， 然后 再 执行 Session #2 。 在 执行 v #2 之后 ， 您 将 看到 ， 如果 没有 UNIQUE 索引 ， 您 可能 会 得到 重复 的 对 (SoftwareName,SoftwareSystemType) 。 会话 #1 的 延迟 用于 为 您 提供 足够 的 时间 来 执行 第 二 个 脚本（ 会话 #2 ） 。 您 可以 减少 此 延迟 。
    • 会话 #1 * * （ SSMS 〉 新建 查询 〉 F5 （ 执行 ） ）
CREATE DATABASE DemoEXISTS;
GO
USE DemoEXISTS;
GO
CREATE TABLE dbo.Software(
    SoftwareID INT PRIMARY KEY,
    SoftwareName NCHAR(400) NOT NULL,  
    SoftwareSystemType NVARCHAR(50) NOT NULL
);
GO

INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (1,'Dynamics AX 2009','ERP');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (2,'Dynamics NAV 2009','SCM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (3,'Dynamics CRM 2011','CRM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (4,'Dynamics CRM 2013','CRM');
INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
VALUES (5,'Dynamics CRM 2015','CRM');
GO
/*
CREATE UNIQUE INDEX IUN_Software_SoftwareName_SoftareSystemType
ON dbo.Software(SoftwareName,SoftwareSystemType);
GO
*/

-- Session #1
BEGIN TRANSACTION;
    UPDATE  dbo.Software
    SET     SoftwareName='Dynamics CRM',
            SoftwareSystemType='CRM'    
    WHERE   SoftwareID=5;

    WAITFOR DELAY '00:00:15' -- 15 seconds delay; you have less than 15 seconds to switch SSMS window to session #2

    UPDATE  dbo.Software
    SET     SoftwareName='Dynamics AX',
            SoftwareSystemType='ERP'
    WHERE   SoftwareID=1;
COMMIT
--ROLLBACK
PRINT 'Session #1 results:';
SELECT *
FROM dbo.Software;

中 的 每 一 个

    • 会话 #2 * * （ SSMS 〉 新建 查询 〉 F5 （ 执行 ） ）
USE DemoEXISTS;
GO
-- Session #2
DECLARE 
    @SoftwareName NVARCHAR(100),  
    @SoftwareSystemType NVARCHAR(50);
SELECT
    @SoftwareName=N'Dynamics AX',
    @SoftwareSystemType=N'ERP';

PRINT 'Session #2 results:';
IF NOT EXISTS(SELECT *
    FROM dbo.Software s
    WHERE s.SoftwareName=@SoftwareName 
    AND s.SoftwareSystemType=@SoftwareSystemType)
BEGIN
    PRINT 'Session #2: INSERT';

    INSERT INTO dbo.Software(SoftwareID,SoftwareName,SoftwareSystemType)
    VALUES (6,@SoftwareName,@SoftwareSystemType);
END 
PRINT 'Session #2: FINISH';
SELECT  * 
FROM    dbo.Software;

格式
结果 ：

Session #1 results:
SoftwareID  SoftwareName      SoftwareSystemType
----------- ----------------- ------------------
1           Dynamics AX       ERP
2           Dynamics NAV 2009 SCM
3           Dynamics CRM 2011 CRM
4           Dynamics CRM 2013 CRM
5           Dynamics CRM      CRM

Session #2 results:
Session #2: INSERT
Session #2: FINISH
SoftwareID  SoftwareName      SoftwareSystemType
----------- ----------------- ------------------
1           Dynamics AX       ERP <-- duplicate (row updated by session #1)
2           Dynamics NAV 2009 SCM
3           Dynamics CRM 2011 CRM
4           Dynamics CRM 2013 CRM
5           Dynamics CRM      CRM
6           Dynamics AX       ERP <-- duplicate (row inserted by session #2)

格式

赞(0）分享  回复(0）举报  2022-11-21
gev0vcfq

gev0vcfq3#

有一个很好的解决这个问题，你可以使用Sql的合并关键字

Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Product Listing Message') temp 
ON temp.Id = hba.Id
WHEN NOT matched THEN 
INSERT (Id, Name) VALUES (temp.Id, temp.Name);

您可以在执行以下操作之前检查此操作，以下是示例

IF OBJECT_ID ('dbo.TargetTable') IS NOT NULL
    DROP TABLE dbo.TargetTable
GO

CREATE TABLE dbo.TargetTable
    (
    Id   INT NOT NULL,
    Name VARCHAR (255) NOT NULL,
    CONSTRAINT PK_TargetTable PRIMARY KEY (Id)
    )
GO


INSERT INTO dbo.TargetTable (Name)
VALUES ('Unknown')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Mapping')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Update')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Message')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Switch')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('Unmatched')
GO

INSERT INTO dbo.TargetTable (Name)
VALUES ('ProductMessage')
GO

Merge MyTargetTable hba
USING (SELECT Id = 8, Name = 'Listing Message') temp 
ON temp.Id = hba.Id
WHEN NOT matched THEN 
INSERT (Id, Name) VALUES (temp.Id, temp.Name);
赞(0）分享  回复(0）举报  2022-11-21
2guxujil

2guxujil4#

更多的是一个建议进一步阅读的评论链接...一个非常好的博客文章，它对完成这项任务的各种方法进行了基准测试can be found here。
他们使用以下几种技术：“在不存在的地方插入”、“合并”语句、“插入Except”和您典型的“左联接”，看看哪种方式完成此任务最快。
用于每种技术的示例代码如下（直接从其页面复制/粘贴）：

INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
-----------------------------------
MERGE #table1 as [Target]
USING  (select Id, guidd, TimeAdded, ExtraData from #table2) as [Source]
(id, guidd, TimeAdded, ExtraData)
    on [Target].id =[Source].id
WHEN NOT MATCHED THEN
    INSERT (id, guidd, TimeAdded, ExtraData)
    VALUES ([Source].id, [Source].guidd, [Source].TimeAdded, [Source].ExtraData);
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT id, guidd, TimeAdded, ExtraData from #table2
EXCEPT
SELECT id, guidd, TimeAdded, ExtraData from #table1
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT #table2.id, #table2.guidd, #table2.TimeAdded, #table2.ExtraData
FROM #table2
LEFT JOIN #table1 on #table1.id = #table2.id
WHERE #table1.id is null

对于那些追求速度的人来说，这是一本好书！在SQL 2014中，Insert-Except方法被证明是处理5000万条或更多记录的最快方法。

赞(0）分享  回复(0）举报  2022-11-21
j13ufse2

j13ufse25#

我知道这篇文章是旧的，但我发现了一个原始的方法，插入值到一个表与关键字INSERT INTO和EXISTS。
我说原创是因为我没有在网上找到它。
这就是：

INSERT INTO targetTable(c1,c2)
select value1,value2
WHERE NOT EXISTS(select 1 from targetTable where c1=value1 and c2=value2 )
赞(0）分享  回复(0）举报  2022-11-21
bzzcjhmw

bzzcjhmw6#

忽略重复的唯一约束不是解决方案吗？

INSERT IGNORE INTO tblSoftwareTitles...
赞(0）分享  回复(0）举报  2022-11-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com