SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query Suggestion please


query Suggestion please

Author
Message
DBA12345
DBA12345
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 17539
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.

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)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28309 Visits: 39955
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
DBA12345
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 17539
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.

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)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39559 Visits: 38554
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search