检查数据是否已存在并插入的更好方法

发布于 2024-11-30 12:24:57 字数 656 浏览 0 评论 0原文

大家好，我有以下 SQL Server 2008 脚本，它将检查表 A 中是否已存在一行以及是否未插入表 B 中的数据。

在表 A 开始填满大量数据之前，这种方法一直运行良好。目前该表中有 3000 万行，并且将继续增长到谓词 7000 万行。

如果这花费的时间太长并且影响其他进程，就会出现问题。只是想知道是否有更好的方法来检查表中是否已存在行。补充一下，这一切都是使用 SSIS 完成的。

脚本：

INSERT INTO TABLE A ([recordID],Field 1, Field2, Field 3, Field 4, Field 5) 
    SELECT 
        [TABLE B].[recordID],[TABLE B].[Field 1], [TABLE B].[Field2], 
        [TABLE B].[Field 3], [TABLE B].[Field 4], [TABLE B].[Field 5] 
    FROM TABLE B AS TABLE B 
    LEFT OUTER JOIN TABLE A AS TABLE A ON [TABLE B].[recordID] = [TABLE A].[recordID] 
    WHERE [TABLE A].[recordID] IS NULL

原文

Hi all I have the following SQL Server 2008 script that will check if a row already exists in Table A and if it doesn't insert the data from Table B.

This was working nicely until Table A started to fill up with a lot of data. We currently have 30 million rows in this table and this will continue to grow to a predicated 70 million rows.

The problem if this is taking far too long and is affecting other processes. Just wondering if there is a better way to check if a row already exists in a table. Just to add as well this is all done using an SSIS.

Script:

INSERT INTO TABLE A ([recordID],Field 1, Field2, Field 3, Field 4, Field 5) 
    SELECT 
        [TABLE B].[recordID],[TABLE B].[Field 1], [TABLE B].[Field2], 
        [TABLE B].[Field 3], [TABLE B].[Field 4], [TABLE B].[Field 5] 
    FROM TABLE B AS TABLE B 
    LEFT OUTER JOIN TABLE A AS TABLE A ON [TABLE B].[recordID] = [TABLE A].[recordID] 
    WHERE [TABLE A].[recordID] IS NULL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

悲喜皆因你 2024-12-07 12:24:57

您可以检查合并命令：

http://technet.microsoft.com/en -us/library/bb510625.aspx

回复收藏 0 原文

很快妥协 2024-12-07 12:24:57

不确定这是否会更快，但值得一试：

INSERT INTO TABLE A ([recordID],Field 1, Field2, Field 3, Field 4, Field 5)

SELECT [TABLE B].[recordID],[TABLE B].[Field 1], [TABLE B].[Field2], [TABLE B].[Field 3], [TABLE B].[Field 4], [TABLE B].[Field 5] 
FROM TABLE B 
where b.recordID not in 
     (select recordID from A)

Not sure if this will be faster, but worth a try:

INSERT INTO TABLE A ([recordID],Field 1, Field2, Field 3, Field 4, Field 5)

SELECT [TABLE B].[recordID],[TABLE B].[Field 1], [TABLE B].[Field2], [TABLE B].[Field 3], [TABLE B].[Field 4], [TABLE B].[Field 5] 
FROM TABLE B 
where b.recordID not in 
     (select recordID from A)

回复收藏 0 原文