• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

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

0
rtay
Asked:
rtay
1 Solution
 
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
 
rtayIT DirectorAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now