January 22, 2007 at 11:11 am
Hi,
I've got a trace table I've created with many statements from a database. I need to get all the rows between the BEGIN TRAN lines and a COMMIT TRAN lines. Is there a way to do this with a sql statement? I could probably figure it out with a proc....
The rows look like this:
(rownumber, sql statment)
102 BEGIN TRANS
103 UPDATE THIS TABLE
104 UPDATE THAT TABLE
105 COMMIT TRANS
106 SELECT SOMETHING
107 BEGIN TRANS
108 UPDATE TABLE
109 UPDATE TABLE
110 COMMIT TRANS
thanks!
Sam
January 25, 2007 at 8:00 am
This was removed by the editor as SPAM
January 25, 2007 at 10:06 am
The question is not very clear.
If there are not too many rows (less that 10,000?) something like the following triangular join may
be what you want. With a lot of rows a cursor will probably be quicker.
-- *********************
-- Start of test data
DECLARE @t TABLE
(
rownumber int NOT NULL PRIMARY KEY
,SQLStatement varchar(4000) NOT NULL
)
INSERT INTO @t
SELECT 102, 'BEGIN TRANS' UNION ALL
SELECT 103, 'UPDATE THIS TABLE' UNION ALL
SELECT 104, 'UPDATE THAT TABLE' UNION ALL
SELECT 105, 'COMMIT TRANS' UNION ALL
SELECT 106, 'SELECT SOMETHING' UNION ALL
SELECT 107, 'BEGIN TRANS' UNION ALL
SELECT 108, 'UPDATE TABLE' UNION ALL
SELECT 109, 'UPDATE TABLE' UNION ALL
SELECT 110, 'COMMIT TRANS'
-- End of Test Data
-- *********************
-- The query, replace @t with your table name.
SELECT T.*
FROM @t T
JOIN (
SELECT D1.rownumber AS BTran
,MIN(D2.rownumber) AS ETran
FROM (
SELECT T1.rownumber
FROM @t T1
WHERE T1.SQLStatement LIKE 'BEGIN TRAN%') D1
JOIN (
SELECT T2.rownumber
FROM @t T2
WHERE T2.SQLStatement LIKE 'COMMIT%' ) D2
ON D2.rownumber > D1.rownumber
GROUP BY D1.rownumber ) D
ON T.rownumber > D.BTran
AND T.rownumber < D.ETran
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply