Creating a table from query - 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

Creating a table from query

Author

Topic

rard
Starting Member

3 Posts

Posted - 2011-12-06 : 23:07:09

I am attempting to create a table from another table using a query. Here is what I have attempted:

create table south_gw
as(select well_name, depth, fc_date, fips_code from well, fluid_lvl
where well.official_name like well_name
and well_name in (6858101, 6829103, 6840401, 6856302, 6861602, 7716801, 7744101, 7731703, 7827503, 7864301, 6746704, 6728104, 8301605)
order by well_name, fc_date);

The query itself works fine. However, when place inside the parenthesis of the 'as' statement it gives an error "missing right parenthesis".

Can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:19:37

it should be


select well_name, depth, fc_date, fips_code
into south_gw 
 from well, fluid_lvl
where well.official_name like well_name
and well_name in (6858101, 6829103, 6840401, 6856302, 6861602, 7716801, 7744101, 7731703, 7827503, 7864301, 6746704, 6728104, 8301605)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rard
Starting Member

3 Posts

Posted - 2011-12-06 : 23:44:26

I ran this exact query and it's giving a "missing keyword" error for the into statement

SQL> select well_name, depth, fc_date, fips_code
2 into south_gw from well, fluid_lvl
3 where well.official_name like well_name
4 and well_name in (6858101, 6829103, 6840401, 6856302, 6861602, 7716801, 7744101, 7731703, 78275
03, 7864301, 6746704, 6728104, 8301605)
5 order by well_name, fc_date;
into south_gw from well, fluid_lvl
*
ERROR at line 2:
ORA-00905: missing keyword

Also, is the order by statement correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:51:18

quote:
Originally posted by rard

I ran this exact query and it's giving a "missing keyword" error for the into statement

SQL> select well_name, depth, fc_date, fips_code
2 into south_gw from well, fluid_lvl
3 where well.official_name like well_name
4 and well_name in (6858101, 6829103, 6840401, 6856302, 6861602, 7716801, 7744101, 7731703, 78275
03, 7864301, 6746704, 6728104, 8301605)
5 order by well_name, fc_date;
into south_gw from well, fluid_lvl
*
ERROR at line 2:
ORA-00905: missing keyword

Also, is the order by statement correct?

aha
so you're using Oracle. You should have specified it
This is ms sql server forum and solutions posted here are mostly sql server specific
Anyways, you can try below modification. if its not working please try your luck in some oracle forums


create table south_gw 
as
select well_name, depth, fc_date, fips_code 
from well, fluid_lvl
where well.official_name like well_name
and well_name in (6858101, 6829103, 6840401, 6856302, 6861602, 7716801, 7744101, 7731703, 7827503, 7864301, 6746704, 6728104, 8301605);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rard
Starting Member

3 Posts

Posted - 2011-12-07 : 00:09:56

Awesome my friend! Thank you for clearing this up for me!
I am a bit new to database management and manipulation and did not know of the differences in oracle and ms sql.
I will keep this in mind. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 00:14:03

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-07 : 06:40:10

Hi,

Check this forum for oracle
https://forums.oracle.com/

Java Database

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources