Tidy text file & set sequence number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tidy text file & set sequence number

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 05:55:16

Dear SQL Team

I have the fun task of importing & manipulating a ‘text’ file extract that isn’t very database friendly.

I have used SSIS to load the data into a staging table.

What I’m left with is sampled below (see eg #LoadedData).

What I know about the text file is that each group of data is separated with @@ - the first thing i want to do is add a sequence number i.e to create the table as exampled; #TidyLoadedData. (there is other data in my file that will allow me to join to master records but I think I need to add the SeqNo first for future data manipulations)

So can anyone advise on how to set a sequence number that increments +1 each time @@ is encountered?

Any help would be great!
Thanks

---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------

CREATE TABLE #LoadedData

(
Field1 nvarchar(4000)
)

GO
SET ANSI_PADDING OFF

Insert into #LoadedData
select '@@' UNION all
select 'lots of free text here here' UNION all
select 'more words' UNION all
select '@@' UNION all
select 'some words' UNION all
select '@@' UNION all
select 'lots more free text typed in here' UNION all
select 'I am typed words' UNION all
select 'this has lots of lines1' UNION all
select 'this has lots of lines2' UNION all
select 'this has lots of lines3'

select * from #LoadedData

--This is an example of the table with data sequenced

CREATE TABLE #TidyLoadedData

(
SeqNo int,
Field1 nvarchar(4000)
)

Insert into #TidyLoadedData

select 1,'@@' UNION all
select 1,'lots of words here' UNION all
select 1,'more words' UNION all
select 2,'@@' UNION all
select 2,'some words' UNION all
select 3,'@@' UNION all
select 3,'lots of free text typed in here' UNION all
select 3,'more words' UNION all
select 3,'this has lots of lines1' UNION all
select 3,'this has lots of lines2' UNION all
select 3,'this has lots of lines3'

select * from #TidyLoadedData

drop table #LoadedData
drop table #TidyLoadedData

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-07 : 07:53:27

Something like this?

But you need to have that identity column so the "row number" populates during the insert.



CREATE TABLE #TidyLoadedData

(
SeqNo int identity(1,1) ,
Field1 nvarchar(4000) null,
SeqNo2 int null

)

Create Unique Clustered Index IX_Load on #tidyloadeddata (SeqNo)

INSERT #tidyloadeddata
select '@@',0 UNION all
select 'lots of words here',0 UNION all
select 'more words',0 UNION all
select '@@',0 UNION all
select 'some words',0 UNION all
select '@@',0 UNION all
select 'lots of free text typed in here',0 UNION all
select 'more words',0 UNION all
select 'this has lots of lines1',0 UNION all
select 'this has lots of lines2',0 UNION all
select 'this has lots of lines3',0

Select * FROM #tidyloadeddata


Declare @SeqNo int
Declare @Seqno2 int 

Set @Seqno2 = 0

Update #tidyloadeddata
SET @SeqNo2 = SeqNo2 = (Case When Field1 = '@@' then @SeqNo2 + 1 else @SeqNo2 end)
	,@SeqNo = SeqNo

FROM #tidyloadeddata
WITH (INDEX(IX_LOAD))

Select * FROM #tidyloadeddata

The results are what you describe as necessary and this will be lightning quick for lots and lots of rows.

Poor planning on your part does not constitute an emergency on my part.

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 08:20:20

Genius! Thanks for your help, got it working in test.

I can’t believe how condensed your final code was.

PS like the signature ;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources