Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select into allways 'on default'?

Posted on 1998-09-30
4
Medium Priority
?
438 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 150 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

If you have come across a situation where you need to find some EDB mailbox recovery techniques, then here you will find the same. In this article, we will take you through three techniques using which you will be able to perform EDB recovery. You …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

916 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