SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 04/16/2013 :  14:17:30  Show Profile  Reply with Quote

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/16/2013 :  23:20:06  Show Profile  Reply with Quote
I can't quite work out what you're doing, but I'm 99% sure you're doing it the hard way and the non-multi-user-aware way.
Try doing it in sets and then check out the MERGE statement.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/17/2013 :  01:14:12  Show Profile  Reply with Quote
are you trying to replicate the records for the server and then update applicationid in cases where its null?
The update makes sense but didnt understand why you're trying to insert records again



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 04/17/2013 :  13:15:58  Show Profile  Reply with Quote
Would a MERGE command work?

djj
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 04/17/2013 :  14:09:17  Show Profile  Reply with Quote
@@Visakh,

Thaks for your reply. Please find the my requirement below and let me know if you need more info.

=======================================================

My requirement is to update the ApplicationID column if Server matches and Insert the server name and ApplicationID data if server has more than one applicationID.

Here is the case:

Server can have multiple App ID's

we need to check as follws.

1. Check the server name matches or not.
2. If server matches check the corresponding App ID. If app ID is null then update
3. If server matches and has multiple APPID's insert servername and AppID

ex:
ApServer-----------

Server|||||AppID
AAABBB|||||1000
AAABBB|||||1005
ASFQRE|||||1001
AGSFSD|||||1002
CCCDDD|||||1003
CCCDDD|||||1009

LUT2013------------

Server|||||AppID
AAABBB|||||NULL
ASFQRE|||||NULL
AGSFSD|||||1002
CCCDDD|||||NULL


OUTPUT TABLE LUT2013 SHOULD LOOKE LIKE BELOW.

Server|||||AppID
AAABBB|||||1000
AAABBB|||||1005
AGSFSD|||||1002
CCCDDD|||||1003
CCCDDD|||||1009
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/18/2013 :  02:02:48  Show Profile  Reply with Quote

UPDATE l
SET AppiD = MaxID
FROM LUT2013 l
INNER JOIN (SELECT Server,MAX(AppID) AS MaxID
            FROM ApServer
            GROUP BY Server
            )a
ON a.Server = l.Server
WHERE l.AppID IS NULL


INSERT LUT2013
SELECT Server,AppID
FROM
(
SELECT Server,MAX(AppID) OVER (PARTITION BY Server) AS MaxID
            FROM ApServer
)t
WHERE AppID <> MaxID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 04/18/2013 :  16:59:14  Show Profile  Reply with Quote
Thank you ...it's got work
I appreciate your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/19/2013 :  02:27:10  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000