Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query Suggestion please Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 12:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
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
Post #1443443
Posted Wednesday, April 17, 2013 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 13,320, Visits: 12,804
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443444
Posted Wednesday, April 17, 2013 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 12,953, Visits: 32,479

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1443449
Posted Wednesday, April 17, 2013 1:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
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

END





Post #1443464
Posted Wednesday, April 17, 2013 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 13,320, Visits: 12,804
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443471
Posted Wednesday, April 17, 2013 1:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 20,857, Visits: 32,875
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse