February 18, 2004 at 2:57 pm
need help after import TXT file in table
i gat one field like this
---------------------------------------------------
****************************
fld1=bbbb ,fld2=aaaa, fld3=dddd
fld4=zzzz,fld5=eeee,fld6=nnnn,
fld7=ddd,fld8=ssss,fld9=0980989,fld11=0u09
****************************
fld1=bbbb ,fld2=aaaa, fld3=dddd
fld4=zzzz,fld5=eeee,fld6=nnnn,
fld7=ddd,fld8=ssss,fld9=0980989,fld11=0u09
****************************
-----------------------------------------------------------
and like this i get 80000 records
how can i do this ???
combining
evry 4 records
between the asterisk(***)
******************************
fld1,+fld2,+fld3,+fld4,+fld5,+fld6,+fld7,+fld8,+fld9,+fld10,+fld11
******************************
February 19, 2004 at 2:49 am
Create Table YourOrigTable(T varchar(8000))
Go
Insert YourOrigTable values('****************************')
Insert YourOrigTable values('fld1=bbbb ,fld2=aaaa, fld3=dddd')
Insert YourOrigTable values('fld4=zzzz,fld5=eeee,fld6=nnnn,')
Insert YourOrigTable values('fld7=ddd,fld8=ssss,fld9=0980989,fld11=0u09')
Insert YourOrigTable values('****************************')
Insert YourOrigTable values('fld1=bbb1 ,fld2=aaaa, fld3=dddd')
Insert YourOrigTable values('fld4=zzz1,fld5=eeee,fld6=nnnn,')
Insert YourOrigTable values('fld7=dd1,fld8=ssss,fld9=0980989,fld11=0u09')
Insert YourOrigTable values('****************************')
GO
Select IDENTITY(int, 1,1) as i,
y.T
Into Orig
from YourOrigTable as y
Go
-- Ignore the last record which will be a NULL
Select
(Select t from Orig as oo Where oo.i=o.i+1)+','+
(Select t from Orig as ooo Where ooo.i=o.i+2)+
(Select t from Orig as oooo Where oooo.i=o.i+3)
From Orig as o
Where o.T='****************************'
Order by i
--
Select o.t+','+
(Select t from Orig as oo Where oo.i=o.i+1)+
(Select t from Orig as ooo Where ooo.i=o.i+2)
From Orig as o
Where Left(o.T,4)='fld1'
Order by i
Go
Drop Table Orig
Drop Table YourOrigTable
Go
February 20, 2004 at 7:20 pm
Heres a loop that will grab everything between the "*" rows, no matter how many rows of data there are (the query above assumes 3 + delimiter).
declare @YOurOrigTable table (ID int identity, T varchar(8000))
Insert @YOurOrigTable values('****************************')
Insert @YOurOrigTable values('fld1=bbbb ,fld2=aaaa, fld3=dddd')
Insert @YOurOrigTable values('fld4=zzzz,fld5=eeee,fld6=nnnn,')
Insert @YOurOrigTable values('fld7=ddd,fld8=ssss,fld9=0980989,fld11=0u09')
Insert @YOurOrigTable values('****************************')
Insert @YOurOrigTable values('fld1=bbb1 ,fld2=aaaa, fld3=dddd')
Insert @YOurOrigTable values('fld4=zzz1,fld5=eeee,fld6=nnnn,')
Insert @YOurOrigTable values('fld7=dd1,fld8=ssss,fld9=0980989,fld11=0u09')
Insert @YOurOrigTable values('****************************')
declare @Destination table (minID int, maxID int, T varchar(8000))
--------------------------------------------------------------------------------
declare @minID int,
@maxID int,
@Dest varchar(8000)
set @MinID = 1
While 1 = 1
begin
select @Dest = NULL
Select top 1 @maxID = [ID]
from @YOurOrigTable
where T like '%*%'
and [ID] > @minID
order by [ID]
IF @@RowCount = 0 Break
Select @Dest = isnull(@Dest + ',', '') + T
From @YOurOrigTable
Where [ID] between @MinID+1 and @MaxID
and charindex('*', T) = 0
Order by ID
Insert @Destination
select @MinID, @MaxID, @Dest
set @minID = @MaxID
END
select * from @Destination
Signature is NULL
February 21, 2004 at 3:01 pm
thnks but how can i insert
80000 records
and loop the insert ???
ilan
February 22, 2004 at 1:01 pm
Need a bit more work!
Declare @Cnt Int,
@MaxRecords Int
Set @Cnt=-10000
Select @MaxRecords=Count(*) From Orig
While @Cnt<=@MaxRecords
Begin
Set @Cnt=@Cnt+10000
Insert SomeTable
Select o.t+','+
(Select t from Orig as oo Where oo.i=o.i+1)+
(Select t from Orig as ooo Where ooo.i=o.i+2)
From Orig as o
Where Left(o.T,4)='fld1' And i >= @Cnt And i < (@Cnt+10000)
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply