检查数据是否已存在并插入的更好方法 [英] Better way to check if the data already exists and insert

问题描述

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

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.

这一直运行良好，直到 Table A 开始填满大量数据.我们目前在这个表中有 3000 万行，这将继续增长到预测的 7000 万行.

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.

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

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.

脚本:

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

推荐答案

可以查看Merge命令:

You can check the the Merge command:

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

这篇关于检查数据是否已存在并插入的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！