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.
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 BEGINDECLARE @MaxRow INTDECLARE @Count INTDECLARE @ServerName NVARCHAR(510)DECLARE @AppCount INTDECLARE @ACount INTDECLARE @ApplicationId NVARCHAR(510)SET @MaxRow =( SELECT MAX(ID) FROM dbo.LUT2013 )SET @Count = 1WHILE (@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 ENDEND ------------------------------------------------------------------ |
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-17 : 13:15:58
|
Would a MERGE command work?djj |
|
|
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'swe 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 update3. If server matches and has multiple APPID's insert servername and AppIDex:ApServer-----------Server|||||AppIDAAABBB|||||1000AAABBB|||||1005ASFQRE|||||1001AGSFSD|||||1002CCCDDD|||||1003CCCDDD|||||1009LUT2013------------Server|||||AppIDAAABBB|||||NULLASFQRE|||||NULLAGSFSD|||||1002CCCDDD|||||NULLOUTPUT TABLE LUT2013 SHOULD LOOKE LIKE BELOW.Server|||||AppIDAAABBB|||||1000AAABBB|||||1005AGSFSD|||||1002CCCDDD|||||1003CCCDDD|||||1009 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-18 : 02:02:48
|
[code]UPDATE lSET AppiD = MaxIDFROM LUT2013 lINNER JOIN (SELECT Server,MAX(AppID) AS MaxID FROM ApServer GROUP BY Server )aON a.Server = l.ServerWHERE l.AppID IS NULLINSERT LUT2013SELECT Server,AppIDFROM(SELECT Server,MAX(AppID) OVER (PARTITION BY Server) AS MaxID FROM ApServer)tWHERE AppID <> MaxID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
aswindba1
Yak Posting Veteran
62 Posts |
Posted - 2013-04-18 : 16:59:14
|
Thank you ...it's got workI appreciate your help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:27:10
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|