Creating temporary table

[Omavlana]

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

[Mauro]

Re: Creating temporary table

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('·',l ogdetail)-1
) as char(2)) as ShmCoy,
cast(substring( logdetail,
charindex('·',l ogdetail)+1,
charindex('·',l ogdetail,charin dex('·',logdeta il)+1)-(charindex('·', logdetail)+1)
) as char(10)) as ShmAcno,
cast(substring( logdetail,
charindex('·',l ogdetail,charin dex('·',logdeta il)+1)+1,
charindex('·',l ogdetail,charin dex('·',logdeta il,charindex('· ',logdetail)+1) +1)-(charindex('·', logdetail,chari ndex('·',logdet ail)+1)+1)
) as varchar(60)) as ShmName1,
cast(substring( logdetail,
charindex('·',l ogdetail,charin dex('·',logdeta il,charindex('· ',logdetail)+1) +1)+1,
charindex('·',l ogdetail,charin dex('·',logdeta il,charindex('· ',logdetail,cha rindex('·',logd etail)+1)+1)+1)-(charindex('·', logdetail,chari ndex('·',logdet ail,charindex(' ·',logdetail)+1 )+1)+1)
) as varchar(60)) as ShmName2
into ##tblabc
from shractivitylog

select * from ##tblabc


Bye!


kiran@boardroom limited.com (Omavlana) wrote in message news:<b14098ab. 0310080226.64bf 03c6@posting.go ogle.com>...[color=blue]
> 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[/color]