Create Copy of Table with Subset of Rows

#1

How do I do 'create table tab1 as select * from tab2
where...' (Oracle syntax) in Sybase?

#2

According to the 9.0.2 docs (SELECT statement, SQL syntax), and not tested:

SELECT *
INTO dba.tab1
FROM tab2

If you can specify the select-list columns, you don't need to specify an
owner-name for the new table:

SELECT c1, c2, c3
INTO tab2
FROM tab2

Richard

<PaulR> wrote

Quote:

How do I do 'create table tab1 as select * from tab2
where...' (Oracle syntax) in Sybase?

#3

Typo: Second example should be:

SELECT c1, c2, c3
INTO tab1
FROM tab2

BTW, Jasper developers, the need for an owner name, when the select-list has
a * instead of specified columns, seems kludgey.

Richard

"Richard Biffl" <sybaseforums (AT) removvethiisphrraseblacklettersoftware (DOT) com>
wrote in message news:4433e608$1 (AT) forums-1-dub (DOT) ..

Quote:

According to the 9.0.2 docs (SELECT statement, SQL syntax), and not
tested:

SELECT *
INTO dba.tab1
FROM tab2

If you can specify the select-list columns, you don't need to specify an
owner-name for the new table:

SELECT c1, c2, c3
INTO tab2
FROM tab2

Richard

PaulR> wrote

How do I do 'create table tab1 as select * from tab2
where...' (Oracle syntax) in Sybase?

#4

Thanks Richard - unfortunately I now get a error :

Syntax error or access violation: near '.' in * into

SQL is :

select * into DBA.client_master_new
from client_master a
where a.effective_date = (select max(b.effective_date)
from client_master b
where b.client_no =
a.client_no)

Paul

Quote:

According to the 9.0.2 docs (SELECT statement, SQL syntax)
, and not tested:

SELECT *
INTO dba.tab1
FROM tab2

If you can specify the select-list columns, you don't need
to specify an owner-name for the new table:

SELECT c1, c2, c3
INTO tab2
FROM tab2

Richard

PaulR> wrote in message
news:4433ccf4.631f.1681692777 (AT) sybase (DOT) com... How do I do
'create table tab1 as select * from tab2 where...'
(Oracle syntax) in Sybase?

#5

PaulR wrote:

Quote:

How do I do 'create table tab1 as select * from tab2
where...' (Oracle syntax) in Sybase?

Richard is right that you can insert to a table with a select (INSERT
.... SELECT), but this won't create the table.

You can use the TSQL way of creating temporary tables:

SELECT *
INTO #my_temp_table
FROM tab2

(though, of course, no one would ever code "*" ... they would explicitly
list the columns their query requires/expects).

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

#6

Allow me to retort... this is a *perfect* place to use * if you want a
complete copy of the table... why on earth would you want to expose
yourself to a coding error such as missing out on a needed column?

On 5 Apr 2006 09:42:10 -0800, Greg Fenton
<greg.fenton_nospam_ (AT) ianywhere (DOT) com> wrote:

Quote:

PaulR wrote:
How do I do 'create table tab1 as select * from tab2
where...' (Oracle syntax) in Sybase?

Richard is right that you can insert to a table with a select (INSERT
... SELECT), but this won't create the table.

You can use the TSQL way of creating temporary tables:

SELECT *
INTO #my_temp_table
FROM tab2

(though, of course, no one would ever code "*" ... they would explicitly
list the columns their query requires/expects).

greg.fenton

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

#7

Is that the *exact* SQL you used? Because this works OK for me in
9.0.2.3267:

select * into DBA.RowGenerator_new
from dbo.RowGenerator a
where a.row_num = (select max(b.row_num)
from dbo.RowGenerator b
where b.row_num = a.row_num)

Breck

On 5 Apr 2006 09:17:26 -0800, PaulR wrote:

Quote:

Thanks Richard - unfortunately I now get a error :

Syntax error or access violation: near '.' in * into

SQL is :

select * into DBA.client_master_new
from client_master a
where a.effective_date = (select max(b.effective_date)
from client_master b
where b.client_no =
a.client_no)

Paul

According to the 9.0.2 docs (SELECT statement, SQL syntax)
, and not tested:

SELECT *
INTO dba.tab1
FROM tab2

If you can specify the select-list columns, you don't need
to specify an owner-name for the new table:

SELECT c1, c2, c3
INTO tab2
FROM tab2

Richard

PaulR> wrote in message
news:4433ccf4.631f.1681692777 (AT) sybase (DOT) com... How do I do
'create table tab1 as select * from tab2 where...'
(Oracle syntax) in Sybase?

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

#8

As of 9.0.1, SELECT ... INTO ... can create a permanent table and populate
it.

Richard

"Greg Fenton" <greg.fenton_nospam_ (AT) ianywhere (DOT) com> wrote

Quote:

Richard is right that you can insert to a table with a select (INSERT
... SELECT), but this won't create the table.

#9

Richard Biffl wrote:

Quote:

As of 9.0.1, SELECT ... INTO ... can create a permanent table and populate
it.

Man, I love these newsgroups...I keep learning and learning and learning...

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

#10

Yes - that's the exact SQL. But I'm using 9.0.0.1108 -
could that be the difference?

Paul

Quote:

Is that the *exact* SQL you used? Because this works OK
for me in 9.0.2.3267:

select * into DBA.RowGenerator_new
from dbo.RowGenerator a
where a.row_num = (select max(b.row_num)
from dbo.RowGenerator b
where b.row_num = a.row_num)

Breck

On 5 Apr 2006 09:17:26 -0800, PaulR wrote:

Thanks Richard - unfortunately I now get a error :

Syntax error or access violation: near '.' in * into

SQL is :

select * into DBA.client_master_new
from client_master a
where a.effective_date = (select max(b.effective_date)
from client_master b
where b.client_no =
a.client_no)

Paul

According to the 9.0.2 docs (SELECT statement, SQL
syntax) >> , and not tested:

SELECT *
INTO dba.tab1
FROM tab2

If you can specify the select-list columns, you don't
need >> to specify an owner-name for the new table:

SELECT c1, c2, c3
INTO tab2
FROM tab2

Richard

PaulR> wrote in message
news:4433ccf4.631f.1681692777 (AT) sybase (DOT) com... How do I
do >> > 'create table tab1 as select * from tab2 where...'
(Oracle syntax) in Sybase?

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book:

http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com