I have a database with tables & views.

Several of the views actually call other views to create Summaries.

I want to grab some of the useful data from this database (about 5 tables only, no views) which I can then work with elsewhere. I need to do this for a number of systems, so I'm trying to figure out how to SCRIPT the whole process.

Of course I should probably have put these tables in their own database first to avoid this! Hindsight is wonderful!

--

My idea is to Backup the database, restore it with a new name & then DROP the bits I don't need.

Something like this:

PREPARE Stmt FROM
'SELECT Name FROM Information.Views  ';
                      
OPEN Crsr;
FETCH FIRST FROM Crsr ('Name') INTO FileName;
WHILE NOT EOF(Crsr) DO
 EXECUTE IMMEDIATE
  ' DROP View "'+FileName+'" ';
 FETCH NEXT FROM Crsr('Name') INTO FileName;
END WHILE;


Is lovely as it DROPs all the VIEWs ... but it fails where a VIEW references other VIEWs. The first cannot be DROPPED until the other is.

Is there a way around this? i.e. I can "ignore constraints" when I EMPTY TABLE, but not on a DROP ... is there some other way of ignoring constraints?

You won't be able to have a short script that automatically does this
because of the dependencies you mention; you'll have to manually craft
your own script so that it all happens in the specific order you
specify.  But as long as you're doing that, why not write a CREATION
script instead of a DROP script?  Do you need to copy the data also?

Just write a script that creates all the tables and views that you need.


--
David Cornelius
Cornelius Concepts

On 11/29/12 06:26, Adam Brett wrote:
> I have a database with tables & views.
>
> Several of the views actually call other views to create Summaries.
>
> I want to grab some of the useful data from this database (about 5 tables only, no views) which I can then work with elsewhere. I need to do this for a number of systems, so I'm trying to figure out how to SCRIPT the whole process.
>
> Of course I should probably have put these tables in their own database first to avoid this! Hindsight is wonderful!
>
> --
>
> My idea is to Backup the database, restore it with a new name & then DROP the bits I don't need.
>
> Something like this:
>
> PREPARE Stmt FROM
> 'SELECT Name FROM Information.Views  ';
>
> OPEN Crsr;
> FETCH FIRST FROM Crsr ('Name') INTO FileName;
> WHILE NOT EOF(Crsr) DO
>    EXECUTE IMMEDIATE
>     ' DROP View "'+FileName+'" ';
>    FETCH NEXT FROM Crsr('Name') INTO FileName;
> END WHILE;
>
>
> Is lovely as it DROPs all the VIEWs ... but it fails where a VIEW references other VIEWs. The first cannot be DROPPED until the other is.
>
> Is there a way around this? i.e. I can "ignore constraints" when I EMPTY TABLE, but not on a DROP ... is there some other way of ignoring constraints?
>

David

Thank you for this suggestion. I needed someone to kick start my thinking.

I do need to copy the data (basically thousands of rowing of "settings" which are useful meta data for a new application) as well as the structure.

However your post made me realise that I can EXPORT just the 2 tables I need and then IMPORT the data into the new DB. This leaves behind all the unnecessary configuration constraints etc.

Stupid of me not to think of it. I was working from a BackUp file rather than a Database, so once it was Restored I didn't think to the step of exporting.

Excellent!  Glad I could, er, be of some sort of inspiration!

--
David Cornelius
Cornelius Concepts

On 11/30/12 01:20, Adam Brett wrote:
> David
>
> Thank you for this suggestion. I needed someone to kick start my thinking.
>
> I do need to copy the data (basically thousands of rowing of "settings" which are useful meta data for a new application) as well as the structure.
>
> However your post made me realise that I can EXPORT just the 2 tables I need and then IMPORT the data into the new DB. This leaves behind all the unnecessary configuration constraints etc.
>
> Stupid of me not to think of it. I was working from a BackUp file rather than a Database, so once it was Restored I didn't think to the step of exporting.
>