Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Question Unanswered: Copy user (schema) within same database

    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.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    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.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jul 2002
    Posts
    42
    Quote Originally Posted by Martijnvs View Post
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by svrider View Post
    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...
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    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...
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Mar 2008
    Posts
    96
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •