create table from select result

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=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)
The exRoute result is like this
70 Ingestion
71 Inhalation/nasal
72 Aspiration (with ingestion)
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 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'
How to create table j9a.j9a has the columns from select
Thanks in advance
Are the columns [Route_Ingestion] and [Route_Inhalation] in the table exRoute?
|||

Hi,

I modified the post,can you please look at that

|||

With your lastest modification, do you still get the same error?

It would be helpful if you were to post the entire set of code. Your error message indicates that somewhere in the code you are requesting something to do with columns named [Route_Ingestion] and [Route_Inhalation]. Perhaps there is an error with the column names somewhere, but without being able to see the entire bit of code, it is impossible to help you.

Msg 207, Level 16, State 1, Line 19
Invalid column name 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'

|||

Hi,

My complete code is

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=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 ('Ingestion',@.1,@.21)

insert into table9 values ('Inhalation',@.2,@.22)

insert into table9 values ('Aspiration',@.3,@.23)

insert into table9 values ('Ocular',@.4,@.24)

insert into table9 values ('Dermal',@.5,@.25)

insert into table9 values ('Bite',@.6,@.26)

insert into table9 values ('Parenteral',@.7,@.27)

insert into table9 values ('Otic',@.8,@.28)

insert into table9 values ('Rectal',@.9,@.29)

insert into table9 values ('Vaginal',@.10,@.30)

insert into table9 values ('Other',@.11,@.31)

insert into table9 values ('Unknown',@.12,@.32)

select * from table9

and Select exrouteid,name from exRoute;

Will give results 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

But still iam getting errors Like

Invalid column name 'Ingestion'.

For all columns this error is coming

Thanks in advance

|||

There is a whole 'mess' of problems here.

First, Table j9a (which comes from exRount) contains does not appear to contain columns named 'Ingestion', 'Inhalation', etc. ( Select exrouteid,name from exRoute ) Those are values stored in the column named 'Name'. Therefore all of the variable assignments using SELECT will fail since they are requesting data from a column named 'Ingestion', etc. ( set @.21=(select count(*) from j9a where Ingestion=1) )

Then you create a table [Table9], which has two (2) columns, (Create table table9(Route varchar(30),Fatal int)) and attempt to INSERT three values into that table. ( insert into table9 values ('Ingestion',@.1,@.21))

There may be additional problems, but I stopped lookiing at this point. Apparantly, you are attempting to so something a bit more complex that your current skills. (Nothing wrong with that, we all have to learn and progress.) But this code cannot work, and is most likely NOT the best way to create a solution.

I suggest that you post the TABLE DDL, some sample data in the form of INSERT Statements, and a description of what you are attempting to accomplish and perhaps we can help you. See this link for help in preparing the information we need in order to help you. http://www.aspfaq.com/5006

|||

Hi,

I found where the problem is.

I modified the stored procedure entirely.

Now iam getting the result

Thanks