Is there a simple way to create a new table with the same structure as a
given one in a *different* database? That means the CREATE TABLE foo AS
SELECT * FROM bar WHERE 1=2 method won't work. Didn't find anything in
some searching. I'd need to have two database handles open.
They're both Oracle, but there's no chance of a cross-database link here.
--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/
Export from database 1
Import into database 2
Truncate table in database 2
-----Original Message-----
From: Peter Scott [mailto:Peter@PSDT.com]=20
Sent: Wednesday, June 06, 2007 2:38 PM
To: dbi-users@perl.org
Subject: Duplicate table structure
Is there a simple way to create a new table with the same structure as a
given one in a *different* database? That means the CREATE TABLE foo AS
SELECT * FROM bar WHERE 1=3D2 method won't work. Didn't find anything =
in
some searching. I'd need to have two database handles open.=20
They're both Oracle, but there's no chance of a cross-database link
here.
--=20
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/
On Thu, 07 Jun 2007 08:47:00 +0200, Dr.Ruud wrote:
> Peter Scott schreef:
>
>> Is there a simple way to create a new table with the same structure
>> as a given one in a *different* database? That means the CREATE
>> TABLE foo AS SELECT * FROM bar WHERE 1=2 method won't work.
>
>> I'd need to have two database handles open.
>> They're both Oracle
>
> I suppose the DBD can give you enough information on the table to
> generate a CREATE statement from that.
Yes, it does. I ended up taking the output of DESCRIBE and tweaking it a
little. I was just using the opportunity to wonder if there was an even
lazier way.
The suggestion of another poster to use dbms_metadata (see, e.g.
http://builder.com.com/5100-6388-5054021.html) was a good one; I'd not
heard of it before. If I have to do more than just column type
definitions, or multiple tables, in the future, I'll definitely check it
out.
--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/
------=_Part_27669_30956024.1181337122754
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
On 6/8/07, Peter Scott
>
>
>
> The suggestion of another poster to use dbms_metadata (see, e.g.
> http://builder.com.com/5100-6388-5054021.html) was a good one; I'd not
> heard of it before. If I have to do more than just column type
> definitions, or multiple tables, in the future, I'll definitely check it
> out.
>
>
dbms_metadata has one nasty flaw - the output must be
manually reformatted to be usable.
Try using DDL::Oracle, as it does work.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
------=_Part_27669_30956024.1181337122754--