UPDATE INSERT code efficiency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE INSERT code efficiency

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-17 : 01:58:06

Not sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:

@ZUserID varchar(10)
AS
SET NOCOUNT ON

DECLARE @counter int
SET @counter = 0
WHILE @counter < 10
	BEGIN
	 SET @counter = @counter + 1
	 IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter) 
	BEGIN	
		UPDATE tblWork SET 
		TransID = Null, 
             	TransCd = Null,
		InvoiceNo = Null,
		DatePaid = Null,
		Adjustment = Null,
		Vendor = Null,
		USExchRate = Null
	WHERE
           		UserID = @ZUserID And LineNumber = @counter
	END
ELSE
          INSERT INTO tblWork
               (LineNumber,TransCd,UserID)
          VALUES
               (@counter,'P',@ZUserID)
END

Kristen
Test

22859 Posts

Posted - 2005-09-17 : 02:15:33

"Not sure what happened to my post,"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55302

First create a Tally table. There are much better methods for doing this to be found elsewhere on SQL Team, but the one I've done here will do as an example. Lots of things can use a tally table, so creating a permanent table will almost certainly be useful for other queries.

Then use that to UPDATE any pre-existing rows, and to INSERT any non-existing rows.


DECLARE @MyNumbersTable TABLE
(
	MyNumberColumn	int NOT NULL,
	PRIMARY KEY
	(
		MyNumberColumn
	)
)

INSERT INTO @MyNumbersTable
SELECT 1 
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10

UPDATE U 
SET 
	TransID = Null, 
     	TransCd = Null,
	InvoiceNo = Null,
	DatePaid = Null,
	Adjustment = Null,
	Vendor = Null,
	USExchRate = Null
FROM	tblWork U
	JOIN @MyNumbersTable
		 ON MyNumberColumn = U.LineNumber
		AND MyNumberColumn BETWEEN 1 AND 10
WHERE
		UserID = @ZUserID

INSERT INTO tblWork
(
	LineNumber,TransCd,UserID
)
SELECT MyNumberColumn, 'P', @ZUserID
FROM	@MyNumbersTable
	LEFT OUTER JOIN tblWork
		 ON UserID = @ZUserID
		AND LineNumber = MyNumberColumn
WHERE	   UserID IS NULL
	AND MyNumberColumn BETWEEN 1 AND 10

Kristen

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-17 : 11:34:17

Thank you. I'll create a dummy UserID on the WORK file and store the 10 records there to replace the UNION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources