Solved

Select into allways 'on default'?

Posted on 1998-09-30
4
386 Views
Last Modified: 2008-02-26
Hi again,

Can I tell sybase where to put my table using select into which creates that table?
0
Comment
Question by:lizzzard
  • 2
4 Comments
 

Expert Comment

by:kevincristo
ID: 1098329

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
 

Author Comment

by:lizzzard
ID: 1098330
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
 
LVL 10

Accepted Solution

by:
bret earned 50 total points
ID: 1098331
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
 

Author Comment

by:lizzzard
ID: 1098332
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now