Solved

Select into allways 'on default'?

Posted on 1998-09-30
4
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying our service is a grudge match between customer benefits and customer pain. In one corner, rolling out fixes (yay!) and delivering new features (double yay!). In the other corner, training on new features (boo – sounds like work), and chan…
The goal of this blog is to: > note what has impeded us from reaching effective life on-call > provide 3 steps to mastering life on-call > highlight what will be achieved with effective life on-call
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 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