Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Store proc Help Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 12:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
Hi, I have written a procedure to update and Insert data by checking row by row. Unfortunately the query updating the records but not inserting the new data. Could you please help to modify the query.

I have to update and Insert the data.
-------------------------------------------------------------------

ALTER PROCEDURE [dbo].[InsUpd_AppId]
AS
BEGIN

DECLARE @MaxRow INT
DECLARE @Count INT
DECLARE @ServerName NVARCHAR(510)
DECLARE @AppCount INT
DECLARE @ACount INT
DECLARE @ApplicationId NVARCHAR(510)
SET @MaxRow =( SELECT MAX(ID) FROM dbo.LUT2013 )
SET @Count = 1


WHILE (@Count <= @MaxRow)
BEGIN
SELECT @ServerName = [SERVER NAME]
,@ApplicationId = [Application ID]
FROM dbo.LUT2013 WHERE LUT2013.Id = @Count

IF EXISTS(SELECT 1 FROM dbo.ApServer WHERE [ServerName] = @ServerName AND @ApplicationId IS NULL)
BEGIN
SELECT ID = IDENTITY(INT,1,1),* INTO #temp FROM ApServer WHERE [ServerName] = @ServerName

SELECT @AppCount = (SELECT MAX(tmp.ID) FROM #temp tmp)
SET @ACount = 1
WHILE(@ACount <= @AppCount)
BEGIN
IF(@ACount = @AppCount)
BEGIN
UPDATE LUT2013
SET LUT2013.[Application ID] = tmp.[Application ID]
FROM LUT2013
INNER JOIN #temp tmp ON tmp.[ServerName] = LUT2013.[Server Name]
AND tmp.ID = @AppCount AND LUT2013.[Application ID] IS NULL
END

ELSE
BEGIN
INSERT LUT2013([Server Name]
,[Application ID]
)
SELECT [ServerName]
,[Application ID]
FROM #temp
WHERE ID = @ACount

END

SET @ACount = @ACount + 1
DROP TABLE #temp
END


END



--To iterate while Loop
SET @Count = @Count + 1



END

END
------------------------------------------------------------------
Post #1442925
Posted Tuesday, April 16, 2013 12:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 22,986, Visits: 31,460
This could most likely be done without a loop. As it appears you are also using SQL Server 2008, may I suggest you take a look at the MERGE statement?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442931
Posted Tuesday, April 16, 2013 12:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
Hi Thanks for your conern..yes it is sql server 2008R2 but i never work with merge statements.

Could you please help me.

Thanks in Advance.
Post #1442934
Posted Tuesday, April 16, 2013 12:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 22,986, Visits: 31,460
DBA12345 (4/16/2013)
Hi Thanks for your conern..yes it is sql server 2008R2 but i never work with merge statements.

Could you please help me.

Thanks in Advance.


Not too familiar with MERGE myself. May have 2008 in development but all code we write has to work in our production environment which is still currently running 2005.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442936
Posted Tuesday, April 16, 2013 12:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,059, Visits: 11,886
I agree with Lynn there is no need for a loop here. MERGE would handle this quite nicely.

http://technet.microsoft.com/en-us/library/bb510625.aspx

If you want/need some help with coding it please take a few minutes to read the first link in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse