Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Insert Records No Nupes

    Hi all,

    Using SSE 2012 64-bit.

    I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.

    This is executing, but is very slow, is there a faster way?

    Code:
    INSERT INTO dbTarget.dbo.tblTarget
    (All fields)
    SELECT
    (All Fields)
    FROM dbSource.dbo.tblSource
    WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
    thx
    w
    Last edited by goss; 03-17-14 at 12:09. Reason: typo

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Best answer I can give with the limited details is this:
    Code:
    ...
    FROM   dbSource.dbo.tblSource
    WHERE  NOT EXISTS (
             SELECT RecordID
             FROM   dbTarget.dbo.tblTarget
             WHERE  RecordID tblSource.RecordID
           )
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check to see if there is an index with RecordId as the first (leftmost) column. If there isn't such an index, create one.

    Then use code like:
    Code:
    INSERT INTO dbTarget.dbo.tblTarget
    (All fields)
       SELECT (All Fields)
          FROM dbSource.dbo.tblSource AS a
          WHERE NOT EXISTS (SELECT *
             FROM dbTarget.dbo.tblTarget AS b
             WHERE  b.RecordId = a.RecordId)
    This should perform significantly better. It will do a single seek against the index instead of constructing a set and searching that set for a member. Multiply that difference by very many rows at all from dbSource.dbo.tblSource and the difference can be huge.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks guys,

    I'll give your suggestions a try with the next INSERT.

    Stats so far with my syntax above

    I had 1.55M unique records in the Target Table
    I need to INSERT unique records from next table containing 2.2M records

    It ran in about 30 minutes, found an additional 701K unique records.

    thx
    w

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, consider batching your inserts.
    You'd think a 1M insert would take the same time as 100x10K inserts, but it won't. The smaller batches will outperform the single, large transaction.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Hi Pat,

    I was on able to create an Index on the Unique Id field
    I checked the field Distinct Count(field) and itretrned the excepted number of records
    But when I try to add the Index to the field I receive this error

    - Unable to create index 'IX_tblTarget'.
    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    thx
    w

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you using SSMS to run your query against SQL Server 2012? If so:
    1. Press Alt-T for the Tools Menu
    2. Press O for the Options menu-item
    3. Navigate as shown in the picture to the General dialog in SQL Query Results
    4. Be sure that the limits are as shown
    -PatP
    Attached Thumbnails Attached Thumbnails SSMS Tools Options.png  
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •