在SQL Server 2005中遇到异常时，继续在临时表中插入数据（也适用于2008）

I have 2 simple tables

我有2个简单的表

+------|---------|----------+
+  ID  |  Value  |   Test   +
+------|---------|----------+
+  3   |   Test  |   12345  +
+  4   |  MyVal  |   56789  +
+  5   |  Another|   101010 +
+------|---------|----------+



+------|---------|----------+
+  ID  |  Value  |   Test   +
+------|---------|----------+
+  3   |   Test  |   12345  +
+  7   |  MyVal12|   56789  +
+  5   |  Another|   101010 +
+------|---------|----------+

The ID from both tables are primary keys.

两个表中的ID都是主键。

In my stored procedure, I create a temporary table like this:

在我的存储过程中,我创建了一个这样的临时表:

CREATE TABLE #tempTable(
        ID int NOT NULL PRIMARY KEY NONCLUSTERED,
        FIELD VARCHAR(255))

and the part of my procedure stored with try-catch block:

以及使用try-catch块存储的程序部分:

BEGIN TRY
INSERT INTO #tempTable(ID, FIELD)
EXEC sp_executesql @myCustomSql, @paramList, @param1, @param2 ...
END TRY

BEGIN CATCH
      // what to put here ?
END CATCH

I want to add rows to my temporary table (you'll see the duplicates for ID = 3 and 5) and I want to continue adding. Finally I want to have the following content for table:

我想在我的临时表中添加行(你会看到ID = 3和5的重复项),我想继续添加。最后,我想为表格提供以下内容:

+------|---------+
+  ID  |  FIELD  +
+------|---------+
+  3   |   Test  +
+  4   |  MyVal  +
+  5   |  Another+ 
+  7   |  MyVal12+
+------|---------+

I gave here a simplified example, in my database I have over one hundred thousand rows and many columns.

我在这里给出了一个简化的例子,在我的数据库中,我有超过十万行和多列。

Thanks

2 个解决方案

#1

To ignore (with a warning) attempts to insert duplicate keys rather than raising an error and having the whole statement fail you can use the IGNORE_DUP_KEY option.

要忽略(带有警告)尝试插入重复键而不是引发错误并使整个语句失败,您可以使用IGNORE_DUP_KEY选项。

CREATE TABLE #tempTable
  (
     ID    INT NOT NULL PRIMARY KEY NONCLUSTERED WITH (IGNORE_DUP_KEY = ON),
     FIELD VARCHAR(255)
  )

#2

Can you not just put a UNION into the @myCustomSQL between the two tables to filter out duplicates?

你能不能将UNION放入两个表之间的@myCustomSQL中来过滤重复项?

If not then maybe a GOTO would be the best for you,

如果没有,那么GOTO可能是最适合你的,

DECLARE @minID int;
SET @minID = 0;

-- Ensure your myCustomerSql only gets records with ID greater than @minID

:CarryOn

BEGIN TRY
INSERT INTO #tempTable(ID, FIELD)
EXEC sp_executesql @myCustomSql, @paramList, @param1, @param2 ...
END TRY

BEGIN CATCH
    IF ERROR_NUMBER() = 2627 -- Error code for duplicate key
    THEN
        SET @min_ID = SELECT MAX(ID) + 1 FROM #TempTable;
        GOTO CarryOn
    ELSE
        --Handle other errors somehow
END CATCH

I wouldn't normally recommend anything as messy as that but you obviously have dirty data already so I'd hope this is a once off to repair it.

我通常不会推荐任何凌乱的东西,但你显然已经有了脏数据,所以我希望这是一次修复它。

#1