Create table from Select - SQL Server Forums

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 from Select

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-21 : 18:15:00

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 Route_Ingestion=1)
set @22=(select count(*) from j9a where Route_Inhalation=1)
set @23=(select count(*) from j9a where Route_Aspiration=1)
set @24=(select count(*) from j9a where Route_Ocular=1)
set @25=(select count(*) from j9a where Route_Dermal=1)
set @26=(select count(*) from j9a where Route_Bite=1)
set @27=(select count(*) from j9a where Route_Parenteral=1)
set @28=(select count(*) from j9a where Route_Otic=1)
set @29=(select count(*) from j9a where Route_Rectal=1)
set @30=(select count(*) from j9a where Route_Vaginal=1)
set @31=(select count(*) from j9a where Route_Other=1)
set @32=(select count(*) from j9a where Route_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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 18:26:45

Well does exRoute have Route_Unknown column?

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 00:38:13

Also, from the way you query, it seems you need to read about cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-22 : 00:44:19

This post shows one more example of why not to use SELECT...INTO over INSERT INTO..SELECT, besides its performance implications.

With SELECT...INTO, since you don't have to explicitly state column names, you don't know what the schema of the table actually is. So my advice will be, CREATE TABLE EXPLICITLY FIRST AND THEN POPULATE IT WITH THE DATA !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Norwich
Posting Yak Master

158 Posts

Posted - 2006-12-22 : 04:08:26

This post lacks some information.
I believe that you are that you are trying to create a Table and the columns of the table are unknown at the time of creation. You will query another table for the column names

If this is correct then you can do the following:
This code is just a guide line and might contain errors.


Create Table j9a
(ID Int)

Declare @SQL Varchar(600),
        @Des varchar(75),
        @Id int

Declare xCurs Cursor for

Select Distinct Description, ID
from exRoute

open xCurs

Fetch next from xCurs into @Des, @Id

While @@Fetch_Status = 0
	Begin
		Set @SQL = 'Alter Table j9a
			    Add ' + @Des + 'varchar(75)'

		Execute (@SQL)
		
		Fetch next from xCurs into @Des, @Id

	End

Close xCurs
Deallocate xCurs

This code select from exRoute then will dynamically add columns from the select to the j9a Table

Regards
N

The revolution won't be televised!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-22 : 04:26:32

quote:
Originally posted by Norwich

This post lacks some information.
I believe that you are that you are trying to create a Table and the columns of the table are unknown at the time of creation. You will query another table for the column names

If this is correct then you can do the following:
This code is just a guide line and might contain errors.

Create Table j9a
(ID Int)

Declare @SQL Varchar(600),
@Des varchar(75),
@Id int

Declare xCurs Cursor for

Select Distinct Description, ID
from exRoute

open xCurs

Fetch next from xCurs into @Des, @Id

While @@Fetch_Status = 0
Begin
Set @SQL = 'Alter Table j9a
Add ' + @Des + 'varchar(75)'

Execute (@SQL)

Fetch next from xCurs into @Des, @Id

End

Close xCurs
Deallocate xCurs

This code select from exRoute then will dynamically add columns from the select to the j9a Table

Regards
N

The revolution won't be televised!

???

Dynamically adding columns? that too from cursor? That is almost 10 times as bad as SELECT...INTO.

BTW, how do you know exRoute table contains metadata information regarding columns?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 05:29:23

quote:
Originally posted by mahimam_2004

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

As Tara already told you, the SELECT statement creates a table with the original names becuase you use asterisk (*) to select the column names.
As the error message above is very clear about, is that column name "Route_Ingestion" is missing! What is it about that you do not understand?
Do a "SELECT * FROM j9a WHERE 1 = 0" and you will see which column names are being used.

Peter Larsson
Helsingborg, Sweden

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-22 : 12:44:03

Hi,
I found where the problem is.
I modified my procedure entirely.Now iam getting the result.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 23:05:26

quote:
Originally posted by mahimam_2004

Hi,
I found where the problem is.
I modified my procedure entirely.Now iam getting the result.
Thanks

What is the problem and how did you solve?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources