Hi.

I think that is better using a stored procedure, but you can try with
this:



create table shractivitylog (logdetail varchar(50))
go

insert into shractivitylog values ('BR··Light Blue Duck··');
insert into shractivitylog values ('·0234578···');
insert into shractivitylog values ('BR··Aqua Duck··');
insert into shractivitylog values ('·0234586···');
insert into shractivitylog values ('UB··Aqua Duck··');

select cast(substring(logdetail,
1,
charindex('·',logdetail)-1
) as char(2)) as ShmCoy,
cast(substring(logdetail,
charindex('·',logdetail)+1,
charindex('·',logdetail,charindex('·',logdetail)+1 )-(charindex('·',logdetail)+1)
) as char(10)) as ShmAcno,
cast(substring(logdetail,
charindex('·',logdetail,charindex('·',logdetail)+1 )+1,
charindex('·',logdetail,charindex('·',logdetail,ch arindex('·',logdetail)+1)+1)-(charindex('·',logdetail,charindex('·',logdetail)+ 1)+1)
) as varchar(60)) as ShmName1,
cast(substring(logdetail,
charindex('·',logdetail,charindex('·',logdetail,ch arindex('·',logdetail)+1)+1)+1,
charindex('·',logdetail,charindex('·',logdetail,ch arindex('·',logdetail,charindex('·',logdetail)+1)+ 1)+1)-(charindex('·',logdetail,charindex('·',logdetail,c harindex('·',logdetail)+1)+1)+1)
) as varchar(60)) as ShmName2
into ##tblabc
from shractivitylog

select * from ##tblabc


Bye!


kiran@boardroomlimited.com (Omavlana) wrote in message news:<b14098ab.0310080226.64bf03c6@posting.google. com>...
> Hi,
>
> How can I create a temporary table say "Tblabc" with column fields
>
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
>
> and fill the table from the data extracted from the statement...
>
> "select logdetail from shractivitylog"
>
>
> The above query returns single value field the data seperated with a '·'
>
> Ex:
> BR··Light Blue Duck··
>
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
>
> I want to do this job with single SQL query. Is it possible. Pls help.
>
>
> Herewith I am providing the sample data
>
> BR··Light Blue Duck··
> ·0234578···
> BR··Aqua Duck··
> ·0234586···
> UB··Aqua Duck··
>
>
> Regards,
> Omav