Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

"select into" - risky ?

4 posts in General Discussion Last posting was on 2009-08-24 16:20:53.0Z
Yog Posted on 2009-08-24 15:34:48.0Z
Sender: e5c.4a92af58.1804289383@sybase.com
From: Yog
Newsgroups: sybase.public.ase.general
Subject: "select into" - risky ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a92b318.ede.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Aug 2009 08:34:48 -0700
X-Trace: forums-1-dub 1251128088 10.22.241.41 (24 Aug 2009 08:34:48 -0700)
X-Original-Trace: 24 Aug 2009 08:34:48 -0700, 10.22.241.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28207
Article PK: 77449

I am moving old historic data to a different database in the
same server using "select into". 55 tables, approx 25m
records in a table.

Example
[code]
select *
into member_orig
from member
where <criteria1>
[/code]

[code]
select *
into member_ar
from member
where not <criteria1>
[code]

Then using sp_rename to rename the tables , then recreate
the indexes, triggers .

The reason I have select-into is because it runs fast with
mimimal downtime. The DBA thinks its too risky to run this.

What other methods are available to move data & is safe &
recoverable ?

Maybe Insert into select with not exists clause ...with
commit points.

Appretiate any inputs on this.

Thank you


"Mark A. Parsons" <iron_horse Posted on 2009-08-24 15:43:14.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: "select into" - risky ?
References: <4a92b318.ede.1681692777@sybase.com>
In-Reply-To: <4a92b318.ede.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090821-0, 08/21/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a92b512$1@forums-1-dub>
Date: 24 Aug 2009 08:43:14 -0700
X-Trace: forums-1-dub 1251128594 10.22.241.152 (24 Aug 2009 08:43:14 -0700)
X-Original-Trace: 24 Aug 2009 08:43:14 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28208
Article PK: 77454

While 'select/into' is not recoverable in the target database, is that *really* an issue? [Just make sure you perform a
full database dump at regular interfals to insure full recoverability to that particular point in time.]

How often does your dataserver 'crash' while running a 'select/into'?

How often does the DBA perform a full database dump of the target database (thus insuring the database can be recovered
to that point in time)?

At this point I'd push the DBA to provide details on why s/he thinks this is *too* risky.

---------------

Also, how often do you plan on running this archival process?

Do you plan on adding to these archive tables at a later date (eg, perform a monthly/quarterly archive of old data from
the primary database to the archive database)? And if so, do you plan on running 'select/into' for new archive tables,
or 'insert/select' into previously populated archive tables?

Yog wrote:
> I am moving old historic data to a different database in the
> same server using "select into". 55 tables, approx 25m
> records in a table.
>
> Example
> [code]
> select *
> into member_orig
> from member
> where <criteria1>
> [/code]
>
> [code]
> select *
> into member_ar
> from member
> where not <criteria1>
> [code]
>
> Then using sp_rename to rename the tables , then recreate
> the indexes, triggers .
>
> The reason I have select-into is because it runs fast with
> mimimal downtime. The DBA thinks its too risky to run this.
>
> What other methods are available to move data & is safe &
> recoverable ?
>
> Maybe Insert into select with not exists clause ...with
> commit points.
>
> Appretiate any inputs on this.
>
> Thank you