Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

Select into allways 'on default'?

Hi again,

Can I tell sybase where to put my table using select into which creates that table?
0
lizzzard
Asked:
lizzzard
  • 2
1 Solution
 
kevincristoCommented:

Hi lizzzard,

First u have to set the sp_dboption "select into" true in that database

sp_dboption dbname,"select into",true (syntax)
go

After that u have to run check point in that database

use user1
go

checkpoint
go


Now you can run the following query

Select * into database.dbo.tablename from tablename
go

Example

use user1(database name)
go

Select * into user2.dbo.employee from employee
go

I hope it will help you.

Thanx


0
 
lizzzardAuthor Commented:
Hi, I think you misunderstood my question. I know how to do a select into, but the problem is that the table always shows up on the default segment. Unless I make a clustered index,ofcourse because that would move the whole table to the segment I define the index on.
The problem here is, that the select into is in a temporary table. I don't want to make an index on it and for some other reasons I don't want it on 'default'.

Regards, Alex
0
 
bretCommented:
The SELECT INTO syntax does not allow you to specify a segment, it always creates the table on default.

As you know, you can create (and then drop, if desired) a clustered index on the table to move it to a specified segment.

You can also use the sp_placeobject stored procedure to cause future allocations to be placed on a named segment, but the existing data will remain on default.

A possible work around is to
1) create the table using select into  where 1=0,
then
2) sp_placeobject on the desired segment,
then populate the table with
3) insert table select * from....
This approach does cause more log activity.
0
 
lizzzardAuthor Commented:
Hi,Bret

The method I use now is indeed the select into on default and the creating the clustered index on the desired segment,thus moving the table. The sp_placeobject might be something to look at.
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now