Clicky

I am doing an INSERT/INTO SELECT using SQL-Server 2008. Encompassing that INSERT/INTO SELECT is a loop that starts with the first row in the source table and then deletes that row based on a successful or failed insert. If the insert for a record fails, then the primary-key for the failed record is logged in the TableErrors table.

All of this works great. My problem is that it's inefficient. I am working with over 400,000-records of data. It just takes too long to run. Looping through one-by-one is the problem. My thought is that perhaps I can make this set-based. The following is the psuedocode:

DECARE @A INT
WHILE EXISTS (SELECT 1 FROM SourceTable)

BEGIN
 SELECT TOP 1 @A=SourceTablePK
 FROM SourceTable

BEGIN TRY
 INSERT INTO TargetTable
 SELECT *
  FROM SourceTable ST
 WHERE [email protected]
END TRY

BEGIN CATCH
 INSERT INTO TableErrors VALUES (@A)
END CATCH

DELETE FROM SourceTable WHERE [email protected];
END

I cannot alter the general structure of the query. Do you have any suggestions?

Thanks!

asked 12/15/2011 04:28

pae2's gravatar image

pae2 ♦♦


7 Answers:
what does SourceTablePK look like?  Is it an integer field?  is it an identity column?
link

answered

knightEknight's gravatar image

knightEknight

assuming then that SourceTablePK is an int field and an identity column (or at least that the value of this field is equal to that of the previous row, plus one), then this will eliminate the need to delete from the source table -- which will be much faster:

DECARE @A INT

select @A = max(SourceTablePK) from SourceTable

WHILE @A > 0
BEGIN
  BEGIN TRY
   INSERT INTO TargetTable
   SELECT *
   FROM SourceTable ST
   WHERE ST.SourceTablePK = @A
  END TRY
  BEGIN CATCH
   INSERT INTO TableErrors VALUES (@A)
  END CATCH

  select @A = @A - 1
END

-- also, make sure there is an index on the SourceTablePK field on the source table.
link

answered 2011-12-15 at 12:37:54

knightEknight's gravatar image

knightEknight

-- preferably a clustered index if there is none:

create unique clustered index X1 on SourceTable (SourceTablePK)
link

answered 2011-12-15 at 12:42:49

knightEknight's gravatar image

knightEknight

-- it just occurred to me that the order of the inserts might be important (0 to max), in which case this solution would be more appropriate.
-- again, assumes that the SourceTablePK is incremental (1,2,3,4,5, ... n)

DECARE @A INT, @M INT

select @A=1, @M = ISNULL(max(SourceTablePK),0) from SourceTable

WHILE @A <= @M
BEGIN
  BEGIN TRY
   INSERT INTO TargetTable
   SELECT *
   FROM SourceTable ST
   WHERE ST.SourceTablePK = @A
  END TRY
  BEGIN CATCH
   INSERT INTO TableErrors VALUES (@A)
  END CATCH

  select @A = @A + 1
END
link

answered 2011-12-15 at 12:43:51

knightEknight's gravatar image

knightEknight

oops  *DECARE should be DECLARE
link

answered 2011-12-15 at 12:48:48

knightEknight's gravatar image

knightEknight

now, if the nature of the problem is that you are getting periodic primary key collisions with the target table, then these can be eliminated using a left join (no loop!)

insert into T
select S.*
from SourceTable S
left join TargetTable T
  on T.TargetPK = S.SourcePK   -- assuming the PK of the target table corresponds to the PK of the source table
where T.TargetPK is null
link

answered 2011-12-15 at 12:49:54

knightEknight's gravatar image

knightEknight

I will certainly try this approach. I am looking for alternatives in addition to this, but I will post that question in another thread. Thanks!
link

answered 2011-12-15 at 12:54:42

pae2's gravatar image

pae2

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×25
×1
×1

Asked: 12/15/2011 04:28

Seen: 292 times

Last updated: 12/15/2011 10:40