April 16, 2013 at 12:19 pm
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
------------------------------------------------------------------
April 16, 2013 at 12:24 pm
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?
April 16, 2013 at 12:27 pm
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.
April 16, 2013 at 12:30 pm
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.
April 16, 2013 at 12:44 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply