使用存储过程搜索和插入数据(如果不存在)

Question

我正在尝试使用存储过程插入数据，同时根据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

Answer 1

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

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

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

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
)