Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partition Results Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 9:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:13 PM
Points: 190, Visits: 637
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.
Post #1450203
Posted Tuesday, May 7, 2013 2:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 820, Visits: 1,175
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?
Post #1450323
Posted Tuesday, May 7, 2013 6:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 7:10 PM
Points: 4,576, Visits: 8,349
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.
Post #1450375
Posted Wednesday, May 8, 2013 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:13 PM
Points: 190, Visits: 637
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.
Post #1450584
Posted Wednesday, May 8, 2013 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:13 PM
Points: 190, Visits: 637
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.
Post #1450586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse