query Suggestion please

Question

query Suggestion please

DBA12345

Right there with Babe

Points: 742
More actions
April 17, 2013 at 12:40 pm

#271284

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
Please give me idea to start working on this requirement.
thanks
Aswin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Please take a few minutes and read the article found at the first link in my signature. With ddl and sample data in a consumable format this is pretty simple.

_______________________________________________________________

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/

Lowell SSC Guru Points: 323504 More actions · Answer 2

my version:

note how i provided ready to paste-and-test code for the table and sample data?

if you can do the same in the future, you will get much better, tested answers.

With YourTable ([Server],[AppID])

AS

(

SELECT 'AAABBB','1000' UNION ALL

SELECT 'AAABBB','1005' UNION ALL

SELECT 'ASFQRE','1001' UNION ALL

SELECT 'AGSFSD','1002' UNION ALL

SELECT 'CCCDDD','1003' UNION ALL

SELECT 'CCCDDD','1009'

)

SELECT T1.*

FROM YourTable T1

INNER JOIN (

SELECT

[Server]

FROM YourTable

GROUP BY [Server]

HAVING COUNT(*) > 1

) T2

ON T1.[Server] = T2.[Server]

ORDER BY

T1.[Server],

T1.[AppID]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBA12345 Right there with Babe Points: 742 More actions · Answer 3

Hi Thanks for your reply...I am not getting the result with your query. I have written like below.

for the belwo proc few folks in this same forum suggesting me to write using merge statement. but I am not getting from any of them. Please check it once and let me know if you need additional info.

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.Lowutilization_2013_1 )

SET @Count = 1

SELECT @MaxRow

WHILE (@Count <= @MaxRow)

BEGIN

SELECT @ServerName = [SERVER NAME]

,@ApplicationId = [Application ID]

FROM dbo.Lowutilization_2013_1 WHERE Lowutilization_2013_1.Id = @Count

IF EXISTS(SELECT 1 FROM dbo.AEG_TBL_AppServer_Pair_2 WHERE [ServerName] = @ServerName)

BEGIN

SELECT ID = IDENTITY(INT,1,1),* INTO #temp FROM AEG_TBL_AppServer_Pair_2 WHERE [ServerName] = @ServerName

SELECT @AppCount = (SELECT MAX(tmp.ID) FROM #temp tmp)

SET @ACount = 1

WHILE(@ACount <= @AppCount)

BEGIN

IF(@ACount = @AppCount)

BEGIN

UPDATE Lowutilization_2013_1

SET Lowutilization_2013_1.[Application ID] = tmp.[Application ID]

FROM Lowutilization_2013_1

INNER JOIN #temp tmp ON tmp.[ServerName] = Lowutilization_2013_1.[Server Name]

AND tmp.ID = @AppCount AND Lowutilization_2013_1.[Application ID] IS NULL

END

ELSE

BEGIN

INSERT Lowutilization_2013_1([Server Name]

,[Application ID]

)

SELECT [ServerName]

,[Application ID]

FROM #temp tmp

WHERE tmp.ID = @ACount

END

SET @ACount = @ACount + 1

END

DROP TABLE #temp

END

--To iterate while Loop

SET @Count = @Count + 1

END

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

Again, if you can post something we can work with we can help you. I would suggest looking at MERGE. It will likely be a good choice here. Certainly a while loop is not going to be the best performance.

_______________________________________________________________

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

DBA12345 (4/17/2013)
Hi Thanks for your reply...I am not getting the result with your query. I have written like below.
for the belwo proc few folks in this same forum suggesting me to write using merge statement. but I am not getting from any of them. Please check it once and let me know if you need additional info.
...

And in that thread (which by the way I just checked and there is nothing more there for us to work with) we asked you for the same things we are asking for here.

If you want help you have to help us. We need the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved and in this case would also require sample data for the update/insert process, expected results based on the sample data.

Without this all you are going to get is guess work. Eventually we may actually hit what you need, but if you help us help you, you will get better answers faster and tested code in return.