Store proc Help

  • 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

    ------------------------------------------------------------------

  • 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?

  • 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.

  • 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.

  • 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