Hi everyone,

Is there a way to copy an entire schema, structure and data, into another within the same database.

We're testing the loading and data of a warehouse.

We're using Sybase ASE 15.

For example, we're loading into a schema called DATAMART, I want to copy it into another called DATAMART_1. That way, users can test the data in the copy while we're loading the original.

Thanks.

JFS.

You can dump a database to a file, and then load that dumpfile in as many databases as you want.
1) dump database datamart to "<folder>/filename.ext"

2) load database datamart_1 from "<folder>/filename.ext"

The databases have to be of the same structure, same devicesize, etc.

Thanks but that's not what I'd like to do.

I want to compy a schema within the same database. Let's say I have a schema DATAMART, I want a copy called DATAMART_1 in the same environment and access it using a different user.

Doable?

Thanks.

JFS.

Ah, I'm sorry, I don't know about schema's in ASE 15. I hope one of the other users here can shine a light on this...

If I understand correct you want something like
select * into DATAMART_1.tableA from DATAMART.tableA

I don't think there is an easy way to copy all 7351 tables
Easiest way would be to create a copy of the database

He mentions 'copy a schema within the same database'. I first thought a copy between databases, but apparently he means something else.
I've never heard of schema's in Sybase...

Hi,

If I am not wrong from schema you means a Login that owns some objects if this is the case then the below query may be helpful for you

select 'select * into '+name +'_new_schema from '+ name from sysobjects where uid=suser_id('schema')

This will generate the sql statements to create the new tables with suffix '_new_schema' for the objects owned by the login 'schema'

execute these sql s one by one and you have the tables with the data.

Hope this will be helpful.


Ishu