Partition Results

Question

Partition Results

UnionAll

SSCrazy

Points: 2102
More actions
May 7, 2013 at 9:00 am

#293110

There is a heap like the one shown below:
USE tempdb;
CREATE TABLE Heap
(
[Column 0] NVARCHAR(100)
);
INSERT Heap ([Column 0])
VALUES ( '01 Hdr' )
,( 'Command(s) completed successfully.' )
,( 'Create Debug Section' )
,( 'New Query' )
,( 'Splendid. Try Me!' )
,( '01 Trl' )
,( '02 Hdr' )
,( 'Command(s) completed successfully.' )
,( 'Create Debug Section' )
,( '02 Trl' );
SELECT * FROM Heap;
This data is from a flat file imported to an extract (staging) table.
I'm trying to write a query that return the following result:
CREATE TABLE Result
([Column 0] NVARCHAR(100)
, PartitionId INT )
INSERT Result ([Column 0], PartitionId)
VALUES ( '01 Hdr', 1 )
,( 'Command(s) completed successfully.', 1 )
,( 'Create Debug Section', 1 )
,( 'New Query', 1 )
,( 'Splendid. Try Me!', 1 )
,( '01 Trl', 1 )
,( '02 Hdr', 2 )
,( 'Command(s) completed successfully.', 2 )
,( 'Create Debug Section', 2 )
,( '02 Trl', 2 );
SELECT * FROM Result
Hdr and Trl stands for header and trailer respectively.
We need to use Substring([Column 0], 4, 3) to determine if it's a header or trailer and match first two characters between header and trailer to find a partition.
I appreciate your help. Please let me know if I need to better explain my question. Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

Let me ask you one question. What I can see from your result that, once you get 'Trl' from Substring([Column 0], 4, 3), every thing else below that will be assigned Partitionid = 2. Is that right?

Sergiy SSC Guru Points: 110206 More actions · Answer 2

You are missing column RowNo in your table Heap.

Files keep the data in physical order as they've been added to the file (because that's a definition of "file").

tables in relational databases do not have such a "natural" order of records.

You need to specify it and upload it from the file together with data.

_____________
Code for TallyGenerator

UnionAll SSCrazy Points: 2102 More actions · Answer 3

Sergiy (5/7/2013)
You are missing column RowNo in your table Heap.
Files keep the data in physical order as they've been added to the file (because that's a definition of "file").
tables in relational databases do not have such a "natural" order of records.
You need to specify it and upload it from the file together with data.

Thank you for this point. There is a row number in the file which I can import. It's a sequential incremental number for each record in the file, new partitions doesn't start with 1.

I'm still unable to write a query for this.

UnionAll SSCrazy Points: 2102 More actions · Answer 4

Neeraj Dwivedi (5/7/2013)
What I can see from your result that, once you get 'Trl' from Substring([Column 0], 4, 3), every thing else below that will be assigned Partitionid = 2. Is that right?

Below the trailer record (we can get it by Substring([Column 0], 4, 3) = 'Trl') will be a new partition. But there can be more than two partitions, so yes, a new partition will start after the trailer record, but not everything else will be assigned Partition = 2.