时间戳

首页 > 解决方案 > 使用存储过程搜索和插入数据（如果不存在）

sql-server - 使用存储过程搜索和插入数据（如果不存在）

问题描述

我正在尝试使用存储过程插入数据，同时基于Customer和进行搜索AccountNumber。有什么办法可以用较短的形式编写以下代码？我应该为此在数据库中创建一个存储过程还是只使用它直接从 VB 插入？

declare @Customer int ,@AccountNumber int

IF NOT EXISTS (SELECT * FROM Table_A 
               WHERE AccountNumber = @AccountNumber AND Customer = @Customer)
BEGIN
    INSERT INTO Table_A 
        SELECT TOP 1 *
        FROM Table_B
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer 
END

IF NOT EXISTS (SELECT * FROM Table_A 
               WHERE AccountNumber = @AccountNumber AND Customer = @Customer)
BEGIN
    INSERT INTO Table_A 
        SELECT TOP 1 *
        FROM Table_C
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer 
END

IF NOT EXISTS (SELECT * FROM Table_A 
               WHERE AccountNumber = @AccountNumber AND Customer = @Customer)
BEGIN
    INSERT INTO Table_A 
        SELECT TOP 1 *
        FROM Table_D
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer 
END

标签： sql-servervb.net

解决方案

结合这里的所有注释是一些更短的代码。注意事项：

  1. INSERT建议始终完整列出语句中涉及的列。它更清楚正在发生的事情，并且如果您有列，也不会导致问题IDENTITY，或者将来更改您的表定义。

  2. TOP 1没有 anORDER BY将返回随机结果，这通常不是您想要的。

    INSERT INTO Table_A 
    SELECT *
    FROM (
        SELECT TOP 1 *
        FROM Table_B
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer
        UNION ALL
        SELECT TOP 1 *
        FROM Table_C
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer
        UNION ALL
        SELECT TOP 1 *
        FROM Table_D
        WHERE AccountNumber = @AccountNumber AND Customer = @Customer
    ) X
    WHERE NOT EXISTS (
        SELECT 1
        FROM Table_A A
        WHERE A.AccountNumber = X.AccountNumber AND A.Customer = X.Customer
    )

推荐阅读