create table structure from select result

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

General SQL Server Forums

New to SQL Server Programming

create table structure from select result

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-21 : 18:39:17

Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null

declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int

set @21=(select count(*) from j9a where Ingestion=1)
set @22=(select count(*) from j9a where Inhalation/nasal=1)
set @23=(select count(*) from j9a where Aspiration=1)
set @24=(select count(*) from j9a where Ocular=1)
set @25=(select count(*) from j9a where Dermal=1)
set @26=(select count(*) from j9a where Bite=1)
set @27=(select count(*) from j9a where Parenteral=1)
set @28=(select count(*) from j9a where Otic=1)
set @29=(select count(*) from j9a where Rectal=1)
set @30=(select count(*) from j9a where Vaginal=1)
set @31=(select count(*) from j9a where Other=1)
set @32=(select count(*) from j9a where Unknown=1)

Create table table9(Route varchar(30),Fatal int)
insert into table9 values ('Route_Ingestion',@1,@21)
insert into table9 values ('Route_Inhalation',@2,@22)
insert into table9 values ('Route_Aspiration',@3,@23)
insert into table9 values ('Route_Ocular',@4,@24)
insert into table9 values ('Route_Dermal',@5,@25)
insert into table9 values ('Route_Bite',@6,@26)
insert into table9 values ('Route_Parenteral',@7,@27)
insert into table9 values ('Route_Otic',@8,@28)
insert into table9 values ('Route_Rectal',@9,@29)
insert into table9 values ('Route_Vaginal',@10,@30)
insert into table9 values ('Route_Other',@11,@31)
insert into table9 values ('Route_Unknown',@12,@32)

select * from table9

The exRoute result is like this
70 Ingestion
71 Inhalation
72 Aspiration
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal

The above giving the errors
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Inhalation'.

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:43:31

Duplicate:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76734

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources