March 17, 2009 at 8:16 am
Hi All,
I have worked out a way to do this, but it isn't very efficient as it is updating a row at a time. I am SURE there is an easier way to do this. At the moment i am missing what it is though!
I want to be able to add a count (in this case 1-7) before resetting back to 1. Then update a current table (#tempaa) with this count...
Here is what i have so far..
--DECLARE @CurrentRow INT
--DECLARE @BatchNo INT
--DECLARE @Idcol INT
--DECLARE @maxbatchNo INT
--
--SET @CurrentRow = 1
--SET @BatchNO = 1
--SET @IDCOL = 1
--SET @maxbatchNo = (SELECT MAX(BatchNo) FROM #tempaa)
--
--WHILE @BatchNO <= @maxbatchNo
--BEGIN
--
--WHILE @CurrentRow <= 7 -- Specifies that we are expecting 7 peices of data for each transaction --
--BEGIN
--
--
--SET ROWCOUNT 1 ---Updates the table one at a time with a count. This gives each peice of data from the log an id assuming its in the same order in each!.
--UPDATE #tempaa
--SET idcol = @Currentrow
--WHERE idcol IS null
--
----PRINT @BatchNO
--SET @CurrentROw = @CurrentRow + 1
--END
--
--SET @BatchNO = @BatchNO + 1
--SET @Currentrow = 1
--
--END
--
--SET ROWCOUNT 0
I know that all you clever people will be able to do better...
VERY much appreciate your help
Thanks
Matt
March 17, 2009 at 10:04 am
If you have a sequential row number column in #tempAA you can update it using the following CTEs.
Can you show us the create table for ##tempaa and maybe some sample data?
;with Tally (N) AS
(SELECT TOP 1000000 ROW_NUMBER() over (order by sc1.id)
FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)
,sequence17 as
(select row_Number() over(order by N) as N, n % 8 as seq17
from tally
where N % 8 > 0
and N < 10000
)
select * from sequence17
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 10:10 am
Thank you for taking the time to reply...The column currently holds NULL's
I literally have no idea what that peice of code that you have given me is doing...:blush:
Thank you though, sorry!
Matt
March 17, 2009 at 10:20 am
No problem. 🙂
Here is a revised version that includes a batch number. Please just run it and look at the results, then I can answer specific questions about it.
It shows three columns. A batch number, a sequential row number, and a [seq17] column that repeats the numbers 1-7 for however many batches (T.N) are specified.
From what I could understand, you wanted to update your #TempAA table with a row number from 1-7 and start all over again. I'm not certain what part your Batch Number plays. If you could post up a sample of what you WANT the #tempAA table to look like it would help the rest of us be certain that we are on the same page with respect to the results we need to produce.
;with Tally (N) AS
(SELECT TOP 1000000 ROW_NUMBER() over (order by sc1.id)
FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)
,sequence17 as
(select n % 8 as seq17
from tally
where N % 8 > 0
and N <= 7
)
select t.N as batch, row_number() over(order by t.N,seq17) as row, seq17
from sequence17 s
cross join tally t
where t.N < 100
order by batch, row, seq17
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 10:57 am
I can understand bits of the logic of what is going on, the actual code format etc that you are using to get the results, and how it is working! it is confusing!. I will do some research and studying so that i understand though. i wouldn't expect you to try and explain it all to me as i haven't used CTE's before.
This is basically a step in a procedure that i am writing to extract log information that has been inserted into a table seperated by a comma. basically the batch no is based on a row being inserted into the log table that would look something like this.
Bob,APP1,V2,1,2008-01-01,6,1
Mark,APP2,V3,2,2008-03-03,5,2
I have extracted the information into a table with the batch no that identifies which line or log it came from. This is based on the assumption that there are 7 peices of information returned by the log into the table.
Here is how the new table looks now (#tempaa)
IdcolMessage Date User Batch
NULLBob 17/03/2009matt1
NULLApp1 17/03/2009matt1
NULLV2 17/03/2009matt1
NULL1 17/03/2009matt1
NULL2008-03-03 17/03/2009 matt1
NULL6 17/03/2009matt1
NULL1 17/03/2009matt1
NULLMark 17/03/2009matt1
NULLApp10 17/03/2009matt2
NULLV2 17/03/2009matt2
NULL1 17/03/2009matt2
NULL1 17/03/2009 matt2
NULL6 17/03/2009matt2
NULL1 17/03/2009matt2
From here i want to insert a recurring count into the idcol column that identifies the actual data that is held from that batch. i.e. the first peice of information is application name, The second would be version etc etc. This will allow me to tie the information together and move it into a table with the appropriate column header so that we can build an application onto the front for querying purposes.
Can i just add an update statement before the last select and join back to #tempaa so that i can update the IDcol?
I hope this explains fully what i am up to. Again i really appreciate your time. my other solution does work but obviously updating a column at a time is not ideal if we are faced with 1000's of rows so i need something more efficient.
Thanks Again.
Matt
March 17, 2009 at 12:17 pm
I think I see what you're driving at, and I think we can get it done in one step without an update. Hang on and I'll see if I can't work it out for you right quick.
If the bit about the tally table is a new concept to you, read up on the Row_Number() function and have a look at Jeff Moden's classic article which is found here[/url]. Basically a tally table is just a list of numbers from 1 to whatever, contained in column [N]. They are useful in many different situations and run very fast.
Basically I used the tally table once to supply batch numbers (t.N) and once for the count of rows 1-7 (N % 8) . The '% 8' operator produces a remainder after a division by 8. If you look closely you will see where I filtered out rows with a remainder of 0. Finally I used the Row_Number function to generate sequential numbers for the [row] column, ordered by batch number and the 1-7 sequence.
Hold tight for new code.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 1:00 pm
Here it is. Many dark and arcane secrets buried within it.....
Seriously, I'm sure you will have questions. First run it, and let's see if it does basically what you need. Send me your questions and I will be happy to walk you through it step by step.
The simplest way for me to think of CTEs are that they are views that only exist for a single query. Obviously they produce result sets which you can treat as if they were "real" tables. You can layer CTEs over CTEs in steps, but the optimizer will take the whole bunch and decide how to most quickly put the results together. I prefer them to derived tables in subqueries because they read from top to bottom.
declare @sample Table (Batch int identity(1,1) primary key, logString varchar(50));
insert into @sample
select 'Adam,APP1,V2,1,2008-01-01,6,1' union all
select 'Bob,APP1,V2,1,2008-01-01,6,1' union all
select 'Carl,APP1,V2,1,2008-01-01,6,1' union all
select 'David,APP1,V2,1,2008-01-01,6,1' union all
select 'Elmer,APP1,V2,1,2008-01-01,6,1' union all
select 'Frank,APP1,V2,1,2008-01-01,6,1' union all
select 'Georgia,APP1,V2,1,2008-01-01,6,1' union all
select 'Henry,APP1,V2,1,2008-01-01,6,1' union all
select 'Inise,APP1,V2,1,2008-01-01,6,1' union all
select 'Jack,APP1,V2,1,2008-01-01,6,1' union all
select 'Kendall,APP1,V2,1,2008-01-01,6,1' union all
select 'Louise,APP1,V2,1,2008-01-01,6,1' union all
select 'Marilyn,APP1,V2,1,2008-01-01,6,1' union all
select 'Norman,APP1,V2,1,2008-01-01,6,1' union all
select 'Oprah,APP1,V2,1,2008-01-01,6,1' union all
select 'Peter,APP1,V2,1,2008-01-01,6,1' union all
select 'Quincy,APP1,V2,1,2008-01-01,6,1' union all
select 'Richard,APP1,V2,1,2008-01-01,6,1' union all
select 'Sarah,APP1,V2,1,2008-01-01,6,1' union all
select 'Tom,APP1,V2,1,2008-01-01,6,1' union all
select 'Umberto,APP1,V2,1,2008-01-01,6,1' union all
select 'Vera,APP1,V2,1,2008-01-01,6,1' union all
select 'Warren,APP1,V2,1,2008-01-01,6,1' union all
select 'Xavier,APP1,V2,1,2008-01-01,6,1' union all
select 'Yolanda,APP1,V2,1,2008-01-01,6,1' union all
select 'Zora,APP1,V2,1,2008-01-01,6,1' union all
select 'Mark,APP2,V3,2,2008-03-03,5,2';
with
Tally (N) AS
(SELECT TOP 1000 ROW_NUMBER() over (order by sc1.id)-- tally table will be from 1 to 1000
FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)
,setup as
(select Batch, ','+logString+',' as logString from @sample)-- put commas at start and stop of logstring before parsing
,magic as
(select Batch, seq17, string
from setup
cross apply (select row_number() over(order by N) as seq17, substring(logString,N+1,charindex(',',logString,N+1)-(N+1)) as string
from tally
where substring(logString,N,1) = ','
and N < len(logstring)
) as dt
)
select Batch,seq17,string from magic
order by Batch,seq17
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 5:54 am
ok, firstly...thank you very much for the response and code. I read through the article on tally tables. Very interesting. Thank you. I can see why they are much better than the traditional loop method! will take a bit of getting used to though.
Secondly, I have studied through the code you have sent, using the online help and re formatted so that i can get my head around it. I think i understand the basic logic of whats going on...correct me if i am wrong tho!
You are building up the information that you need as you go along, first getting the tally count up to 1000 which is used both for the batch and the sequence and calling it (N) (why do you reference this as "N" specially? rather than just naming it within tally?), you then pad out both sides of the data with commas so that you know where to start and finish
Then ordering by the sequence you extract everything between the two commas. [Where substring(logString,N,1) = ','] gives it a starting position and the [N < len(logstring)] gives it a finishing spot.
now the questions....
whats are the " ; " for?
I have read the explanation for cross apply, but am still al little unsure..how does this bit work?
The rest, although very clever i think i understand what you are doing and how you are getting the results. Whether i could actually write this is another matter, at least i understand it tho!
Thank you so much for your help with this. I am pretty sure that it will give me what i need
Your a star 😀
Matt
March 18, 2009 at 7:43 am
sorry..wrote another question, but then answered it myself! ignore this!
March 18, 2009 at 10:05 am
Semicolons mark the end of an SQL statement... while they are not currently required, I've heard rumors that they will be in the future You can leave them out and it will run. the same, however the word "WITH" (when used to start a series of CTES) MUST follow a semicolon.
As for "N"... that's just the column name I learned from the examples I read about. Either of the following work to assign column names in CTEs.
;With tally (N) as
( select row_Number() .....
-- or
;With tally as
( select row_Number() ... AS N, ....
Finally... cross apply. This is a tough one for me to explain but I'll try. Cross Apply is new and is used to "join" either an inline table valued function (ITVF*) or a subquery to a table. The practical effect is that it gives the optimizer a chance to come up with a faster scheme to deliver the results rather than just running the subquery or function once for each row in the result set.
(*We can talk more about ITVFs another time.)
Have you read about how joining two tables based on a calculated value is slow?
select tableA.[salePrice]*tableA.[saleQty] - tableB.[Discount]
from tableSales
join tableDiscounts on tableA.[salePrice]*tableA.[saleQty] -- not a happy thing
We're always warned to join on columns, or variables, or constants. Even implicit conversions of datatypes can sometimes slow the join down? Sometimes it's unavoidable, and I've seen instances where Cross Apply together with a subquery ran faster than the join. I think you'll agree that the code I sent you runs pretty quick.
By the way, I'm happy to be of help, but I'm hardly a star. Most everything I showed you I've learned since I started hanging around SSC. Standing on the shoulders of giants and all that. You can get a real education in SQL just reading the various questions asked in the forums and seeing how they are answered.
Good luck to you 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply