SQL Server Stored Procedure, update / insert

I need a little help with a stored procedure.  I have two tables with exact same structure.

Table A                                   Table B

aID  INT                                                     bID   INT
TruckNo  nvarchar                                  TruckNo nvarchar
FieldNo   nvarchar (unique)                   FieldNO nvarchar (unique)


Table A is fed daily from .CSV through SSIS.  In that same file are record corrections from the day before.  SSIS checks for Duplicate FieldNo and moves those duplicate records to Table B.  The user will review the records in Table B and if approved, trigger a stored procedure to update the records in Table A that have matching FieldNo, then delete the record from Table B.  

I have a stored procedure I worked on, but something is wrong.  With only 10 records in Table B the SP keeps working for over 30 min.  No error, it just seems stuck in a loop and does not make any changes to Table A.  

An suggestions are welcome.  


ALTER PROCEDURE [dbo].[OverwriteCorrections]
 
  AS

  DECLARE  @rows INT,
		   @TruckNo nvarchar,
		   @FieldTag nvarchar

  SELECT
		   @rows = COUNT(1)

  FROM

	    tbl_Corrections

  WHILE (@rows > 0)

  BEGIN

  SELECT TOP 1

		@TruckNo = TruckNumber,
		@FieldTag = FieldTagnum

  FROM

	tbl_Corrections

	IF EXISTS (SELECT * FROM tbl_ImportExtAG WHERE FieldTagnum = @FieldTag)

		UPDATE

		tbl_ImportExtAG

		SET

		TruckNumber=@TruckNo

		WHERE 

		FieldTagnum=@FieldTag


	ELSE

	 INSERT INTO
		tbl_ImportExtAG(TruckNumber,FieldTagnum)

		VALUES
			(@FieldTag,@TruckNo)


	DELETE FROM

		tbl_Corrections

	WHERE

	     FieldTagnum=@FieldTag

	SELECT

	     @rows = COUNT(1)

		 FROM tbl_Corrections

		 end

Open in new window

LVL 5
rtayIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You need to use set-based processing rather than row by row.  Like this:


ALTER PROCEDURE [dbo].[OverwriteCorrections]
 
  AS

  SET NOCOUNT ON;

  --update matching rows
   
  UPDATE

            iea

            SET

            TruckNumber=c.TruckNumber
             
            FROM
                     
                     tbl_Corrections c
                     
            INNER JOIN

                     tbl_ImportExtAG iea
           
            ON
           
                     c.FieldTagnum = iea.FieldTagnum

   

  --insert non-matching/new rows

  INSERT INTO

           tbl_ImportExtAG(TruckNumber,FieldTagnum)
           

           SELECT
           
                     c.TruckNumber,c.FieldTagnum
           
           FROM

                     tbl_Corrections c

           LEFT OUTER JOIN

                     tbl_ImportExtAG iea
           
            ON
           
                     c.FieldTagnum = iea.FieldTagnum
                     
            WHERE
           
                     iea.FieldTagnum iS NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtayIT DirectorAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.