October 30, 2008 at 6:16 am
rbarryyoung: (p.s. This is "Humor" also.)
Now, that's funny!
October 30, 2008 at 6:46 pm
jlcampos71 (10/29/2008)
Thanks Again Jeff!I almost Figured that out... but I didn't at all... I was figuring out that I should make in fact, 3 passes, because there is a second constriction in case the key already exists...
But yes, you are right... my SP would take the data from the Split table (@Elements) and insert or update according to your suggestion.
Thanks again!!!
Jorge Luis
You bet, Jorge. Thanks for the feeback.
Also, my recommendation would to see what happens to performance if you convert @Elements to #Elements... 😉
--Jeff Moden
October 31, 2008 at 8:56 am
I'll do that Jeff... but please let me know... where can I found information about it?
As I told you, I'm a very beginner next to you...
Is there anything in the BOL or something you have wrote about it?
Just to let you know, I have implemented your solution already, After having the @elements table populated, I insert all the data into another table defined as @Ubicacion with the structure I need, and then I do Two different update operations (one condition is managed like a WHERE and the other as an AND in the Join condition) and one INSERT to manage all my diferent cases. Let me show you:
--===== Updates the values that already exists and has different antena.
-- Updates the value of previous antena with current antena
UPDATE Dest
SET ubiIdChip = temp.ubiIdChip,
ubiIdAntenaAnt = dest.ubiIdAntena,
ubiIdAntena = temp.ubiIdAntena,
ubiFecha = temp.ubiFecha,
ubiBateria = temp.ubiBateria
FROM Ubicacion Dest
INNER JOIN @Ubicacion temp
ON Dest.ubiIDChip = temp.ubiIdChip
WHERE dest.ubiIdAntena <> temp.ubiIdAntena
--===== Updates the values that already exists and has the same antena.
-- Current and Previous antena remains the same.
UPDATE Dest
SET ubiIdChip = temp.ubiIdChip,
-- ubiIdAntenaAnt = ubiIdAntena,
-- ubiIdAntena = temp.ubiIdAntena,
ubiFecha = temp.ubiFecha,
ubiBateria = temp.ubiBateria
FROM Ubicacion Dest
INNER JOIN @Ubicacion temp
ON Dest.ubiIDChip = temp.ubiIdChip AND Dest.ubiIdAntena = temp.UbiIdAntena
--===== Insert the values that doesn't exists
-- Previous antena set to -1.
INSERT INTO Ubicacion
SELECT temp.*
FROM @Ubicacion temp
LEFT JOIN Ubicacion Dest
ON Dest.ubiIDChip = temp.ubiIdChip
WHERE Dest.ubiIdChip is NULL
Would you mind to review it?
Thanks again!
Jorge Luis
October 31, 2008 at 5:29 pm
jlcampos71 (10/31/2008)
I'll do that Jeff... but please let me know... where can I found information about it?
Yeaup... start by looking up "temporary tables [SQL Server]" (without the quotes) in Books Online. Under the subcategory of "ccreating", they have a section that explains a bit about temp tables. Also, see the following URL...
Even thought it's an SQL Server 2000 URL, it's still pretty much spot on.
--Jeff Moden
October 31, 2008 at 5:52 pm
Jorge,
I flattered that you've mimiced the format I use for comments. And, they're pretty much what I would expect to see in a code review.
There are, however, a couple of potential problems with the following code...
--===== Insert the values that doesn't exists
-- Previous antena set to -1.
INSERT INTO Ubicacion
SELECT temp.*
FROM @Ubicacion temp
LEFT JOIN Ubicacion Dest
ON Dest.ubiIDChip = temp.ubiIdChip
WHERE Dest.ubiIdChip is NULL
The comments say that the "Previous antena {will be} set to -1", but I don't see that in the code anywhere.
The other problem is that you should probably include a column list in both the Insert and the Select. Some folks call it a "best practice". I won't go that far, but it is something that I make the habit of doing... that way if the import order of columns changes on the temp table, you don't have to worry about data ending up in the wrong columns on the Ubicacion table. It's just one more "safe guard".
--Jeff Moden
November 4, 2008 at 8:15 am
J (10/30/2008)
rbarryyoung: (p.s. This is "Humor" also.)Now, that's funny!
Sitting in a cubicle farm, I tried (unsuccessfully) to not laugh out loud.
Thanks -- it made my day. 🙂
Paul DB
November 4, 2008 at 9:26 am
Jeff Moden (10/31/2008)
Jorge,I flattered that you've mimiced the format I use for comments. And, they're pretty much what I would expect to see in a code review.
Yes, I never though comments should be important in SQL and SPs although I do it a lot while programming in VB.Net, but after reading some of your articles, I realized that it is REALLY Important and nothing better than do it the way the master does. So... Thanks for that.
There are, however, a couple of potential problems with the following code...
The comments say that the "Previous antena {will be} set to -1", but I don't see that in the code anywhere.
I missed that part, there is a place where I set to -1 that column, let me show you... just before the code I pasted here there is this other part of code (commented using the Jeff's way of course)
--===== Insert the final result of the split
-- as a "table" instead of an "EAV" into @Ubicacion
INSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria)
SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip,
MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena, -1,
CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha,
MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria
FROM @Elements
GROUP BY RowNum
As you can see, third value is always a (-1) for the ubiAntenaAnt column, I was wondering if this was the best way or using maybe as a variable set to this value and using it only in the insert clause, maybe that is the best way, because I would reduce the size of that table... let me read what you think... please.
The other problem is that you should probably include a column list in both the Insert and the Select. Some folks call it a "best practice". I won't go that far, but it is something that I make the habit of doing... that way if the import order of columns changes on the temp table, you don't have to worry about data ending up in the wrong columns on the Ubicacion table. It's just one more "safe guard".
Got it! I think that way too... it was a lazy second I guess!!! Fixed!
Thanks again!
Jorge Luis
November 4, 2008 at 6:58 pm
Some will take exception to it, but my feeling is that if it will never me anything but a (-1), then hardcoding it makes it pretty clear when it comes to readability. I would also put a column alias on it just to increase the readability... maybe even with a comment so no one in the future has to guess.. like this (dunno if the comment is correct, though)...
--===== Insert the final result of the split
-- as a "table" instead of an "EAV" into @Ubicacion
INSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria)
SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip,
MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena,
-1 AS ubiIdAntenaAnt, --Previous antena does not exist
CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha,
MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria
FROM @Elements
GROUP BY RowNum
Of course, you could make it self documenting with a variable/constant
DECLARE @NothingPrevious INT
SET @NothingPrevious = -1
--===== Insert the final result of the split
-- as a "table" instead of an "EAV" into @Ubicacion
INSERT INTO @Ubicacion (ubiIdChip, ubiIdAntena, ubiIdAntenaAnt, ubiFecha, ubiBateria)
SELECT MAX(CASE WHEN ColNum = 1 THEN ElementValue END) AS ubiIdChip,
MAX(CASE WHEN ColNum = 2 THEN ElementValue END) AS ubiIdAntena,
@NothingPrevious AS ubiIdAntenaAnt,
CAST(CAST(MAX(CASE WHEN ColNum = 3 THEN ElementValue END) AS Float) AS DateTime) AS ubiFecha,
MAX(CASE WHEN ColNum = 4 THEN ElementValue END) AS ubiBateria
FROM @Elements
GROUP BY RowNum
I don't believe it will matter much for speed either way, but I've not tested this bit of code for performance.
--Jeff Moden
November 4, 2008 at 10:19 pm
Thanks Again Jeff!!
I was considering the last option you mentioned. I will do it that way...
Thanks
Jorge Luis
November 5, 2008 at 6:06 pm
Thanks for the feedback, Jorge. Let us know if there's anything else we can do to help.
--Jeff Moden
February 14, 2009 at 8:55 am
Something you might find useful, as I did, to improve the performance.
T-SQL can split fixed width columns much faster than splitting on delimitters.
For large csv strings, it is actually more efficient to do a clr function to convert a csv string in to a fixed width string.
Eg
declare @csvstr varchar(max)
declare @fixedstr varchar(max)
declare @fixedlength int
set @csvstr = ...
set @fixedlength = 20
-- convert to csv fixed width
set @fixedstr = dbo.fnCsvToFixedClr(@fcsvstr, @fixedlength)
-- now simply split the fixed width string using the tally table (my tally table starts at zero, if your one starts at one use substring(@fixedstr, N * 20 - 19, 20) instead)
select substring(@fixedstr, N * 20 + 1, 20)
from dbo.Tally
where N <= (len(@fixedstr) -1 ) / @fixedlength
February 14, 2009 at 9:11 am
To update a batch of many rows at once, you might consider using the .Net SqlBulkCopy class (if you are using .net) class in to a table. It takes a DataSet and given a connection and a table name, will run a bulk insert operation (very very fast).
If like me you prefer to have control within a stored procedure, just in case you ever want to add some extra logic, use bulk copy to get the data in to an import table (might be able to use #tables, though have not tried). Then, call the stored proc with no arguments, which could then process the new/updated rows, and empty the import table.
Sam
February 14, 2009 at 10:04 am
True in both cases. Thanks for the feedback, Sam.
--Jeff Moden
January 8, 2010 at 2:35 am
In SQL Server 2008 you most certainly CAN parse a table variable as a parameter. When used in conjunction with visual studio 2008 you can even parse it from your code.
I often parse XML from my application to my pre-2008 databases but I can see the merit of using this method in 2005 when parsing data from one SP to another.
January 8, 2010 at 5:01 am
Excellent article.
BTW I have another version of CTE, which splits delimited string without tally table. I think it works efficiently, also. Please have a look...:-)
DECLARE
@list VARCHAR(MAX),
@delim CHAR(1)
SET @delim = ','
SET @list = REPLICATE(CAST('123456,' AS VARCHAR(MAX)),10000) + '123456'
SET STATISTICS TIME ON
;WITH csvtbl(start, stop) AS (
SELECT start = CONVERT(bigint, 1),
stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
UNION ALL
SELECT start = stop + 1,
stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
@list + @delim, stop + 1)
FROM csvtbl
WHERE stop > 0
)
SELECT LTRIM(RTRIM(SUBSTRING(@list, start, CASE WHEN stop > 0 THEN stop - start ELSE 0 END))) AS Value
FROM csvtbl
WHERE stop > 0
OPTION (MAXRECURSION 0)
SET STATISTICS TIME OFF
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply