CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssis
Vince Ashby-Smith
Vince Ashby-Smith

Reputation: 1192

Better way to check if the data already exists and insert

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

Upvotes: 2

Views: 1879

Answers (3)

teenboy
teenboy

Reputation: 368

  1. If it is clustered Index, and the newly added RecordId is not incremental, then lot of page spilt is expected to happen. Make sure to set optimal Fill Factor.
  2. Find what operation takes time, then it will be simple to address. If it Searching is taking time or inserting is taking time?

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29619

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)

Upvotes: 3

Pleun
Pleun

Reputation: 8920

You can check the the Merge command:

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

Upvotes: 3

Related Questions