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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query help

Author  Topic 

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-04-16 : 14:17:30

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 - 2013-04-16 : 23:20:06
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

52326 Posts

Posted - 2013-04-17 : 01:14:12
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

352 Posts

Posted - 2013-04-17 : 13:15:58
Would a MERGE command work?

djj
Go to Top of Page

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-04-17 : 14:09:17
@@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

52326 Posts

Posted - 2013-04-18 : 02:02:48
[code]
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
[/code]

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

aswindba1
Yak Posting Veteran

62 Posts

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:27:10
welcome

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

- Advertisement -