December 8, 2009 at 1:56 am
Hi All,
How do I update the Live temp table using the Maint table with the last row values that is NOT NULL for Message and Description columns.
CREATE TABLE #MaintTable (
RowID int NOT NULL,
SomeID int NOT NULL,
[Message] varchar(50),
Description nvarchar(250),
[Action] char(1) NOT NULL
)
GO
CREATE TABLE #LiveTable (
RowID int NOT NULL,
SomeID int NOT NULL,
[Message] varchar(50),
Description nvarchar(250),
[Action] char(1) NOT NULL
)
GO
INSERT #MaintTable
SELECT 1, 1111111, 'Message1', 'Desc1', 'M' UNION ALL
SELECT 1, 1111111, NULL, 'Desc2', 'M' UNION ALL
SELECT 1, 1111111, 'Message3', 'Desc3', 'M' UNION ALL
SELECT 1, 1111111, NULL, 'Desc4', 'M'
GO
INSERT #LiveTable
SELECT 1,1111111,'Message','Desc1','M'
GO
SELECT * FROM #MaintTable
SELECT * FROM #LiveTable
GO
DROP TABLE #MaintTable
DROP TABLE #LiveTable
GO
The output on the Live temp table should be as follows:
SELECT '1' as RowID, '1111111' as SomeID, 'Message3' as Message, 'Desc4' as Description, 'M' as Mode
Thanks you!
- James
December 8, 2009 at 4:36 am
Hi James, good to see data in a consumable format but I am afraid the requirement you are giving is insufficient. Its an attempt to answer your question and give you some idea. But I would expect you to come back with real scenario.
If you want to just get the latest record from the maint table and keep
it in live table, i would suggest you to do this,
TRUNCATE TABLE #LIVETABLE
INSERT INTO #LIVETABLE
SELECT TOP 1 *
FROM #MaintTable
Where MEssage iS NOT NULL
AND DESCRIPTION IS NOT NULL
ORDER BY [Message] DESC
But usually we dont find the latest record based on 'message' , so there
must be some timestamp column?
If you want to update message and description what would you want to
do with the rest of the column? That being said, this is a crude way of
what you are really looking for,
;With CTE ([Message], [Description]) AS
(SELECT TOP 1 [Message], [Description]
FROM #MaintTable
Where MEssage iS NOT NULL
AND DESCRIPTION IS NOT NULL
ORDER BY [Message] DESC)
UPDATE #LIVETABLE
SET Message = (SELECT [Message] FROM CTE),
DESCRIPTION = (SELECT [Description] FROM CTE)
But I am sure thats not what you want?
Hope this helps. I would expect you to come back with some more info 🙂
---------------------------------------------------------------------------------
December 8, 2009 at 5:12 am
Keep in mind there is no order in a set !
So you have to define "last" using some criteria !
Not having such criteria will cause "random" results, in many cases not providing what you aim for.
As Nabha stated, it is best to add a column containing e.g. datetime or sequence info. You could add this column using an alter table and specifying a default getdate().
e.g. alter table yourtable add tsregistration datetime NOT null default getdate().
However, you will have to consider if rows may get inserted at the same time, maybe resulting in multiple rows having the same datetime value.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2009 at 5:14 am
SELECT ROW_NUMBER() OVER(PARTITION BY rowid ORDER BY rowid) as rownum , * INTO #MaintTable1 FROM #MaintTable
DECLARE @cnt int
SELECT @cnt = max(rownum) FROM #MaintTable1
WHILE @cnt >= 1
BEGIN
IF EXISTS (SELECT * FROM #MaintTable1 WHERE rownum = @cnt AND [Message] IS NOT NULL)
BEGIN
UPDATE L SET L.[Message] =A.[Message] FROM #LiveTable L JOIN #MaintTable1 A ON L.rowid = A.rowid
WHERE rownum = @cnt AND A.[Message] IS NOT NULL
BREAK;
END
SET @cnt = @cnt -1
END
SELECT @cnt = max(rownum) FROM #MaintTable1
WHILE @cnt >= 1
BEGIN
IF EXISTS (SELECT * FROM #MaintTable1 WHERE rownum = @cnt AND [Description] IS NOT NULL)
BEGIN
UPDATE L SET L.[Description] =A.[Description] FROM #LiveTable L JOIN #MaintTable1 A ON L.rowid = A.rowid
WHERE rownum = @cnt AND A.[Description] IS NOT NULL
BREAK;
END
SET @cnt = @cnt -1
END
December 8, 2009 at 5:20 am
Nabha - Thank you for your prompt response. Well the result is not what I want, I just need to get the last value of the columns Message and Description that are NOT NULL.
This is actually part of the rule that our existing system needs as per our customer requirement. Sorry can't give enough details since it is proprietary. In this regard, is it possible to do it without using the ORDER BY clause since I am dealing with a huge amount of data like 750k to Millions and that I believe would cause performance issue, correct me if I'm wrong.
December 8, 2009 at 5:44 am
James Tech (12/8/2009)
Nabha - Thank you for your prompt response. Well the result is not what I want, I just need to get the last value of the columns Message and Description that are NOT NULL.This is actually part of the rule that our existing system needs as per our customer requirement. Sorry can't give enough details since it is proprietary. In this regard, is it possible to do it without using the ORDER BY clause since I am dealing with a huge amount of data like 750k to Millions and that I believe would cause performance issue, correct me if I'm wrong.
You are wrong ...
Picture this :
You have a pile of paper scattered all over the place. ( put a huge fan in the place and turn it on at maximum speed .... this way you can emulate fragmentation / page splits )
Tell me which paper is the latest ?
So now you are telling me, my pile of paper is huge, it will be obvious which paper is the latest.
Well .... not for any rdbms unless you tell it HOW to determine "latest".
If you know your "latest" cryteria and fear a performance hit, support it with a couvering index !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 8, 2009 at 5:57 am
ALZDBA has given you a good example. How'd you tell SQL Server what is the 'last' message?
---------------------------------------------------------------------------------
December 8, 2009 at 7:14 pm
Good point there ALZDBA, since those are the only columns that exist on our db table..there should be index in where you can determine the latest one. I just wonder if partitioning the RowId make send and rank() it.
Any thoughts? or is there any other way to generate it on the fly and insert timestamp?
December 9, 2009 at 12:22 am
If RowId is an incremental value it may be used.
I hope it is a column of datatype BIGint because chances are that will last for a very long time (if increment by 1).
Anyways, document that this column is being used to determine young/old order so everyone working with your data will know it. (and to avoid anyone from filling sequence gaps !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply