Krung,
The fastest way to do a table transfer is one of two methods (depending on
your DBMS, and whether or not the target table already exists).
If you do not have timestamp columns, or any non-transferable columns, and
Source and Target databases can establish a direct connection between the
two, and the columns are in the same order, then you can use:
INSERT INTO Target..Table
SELECT * FROM Source..Table
Granted, I used the naming convention of the DBMS I use most, so you might
check the naming convention of the DBMS you are using to see how database
and table are designated, and if you are going to a different server, there
may be an additional step still. (like Server.Database..Table)
If the target table does not already exist, you might check your DBMS docs
and see if a SELECT...INTO is supported. This looks like:
SELECT * INTO Target..Table
FROM Source..Table
That command creates the destination table, then copies all the contents
into it.
If you have nontransferrable columns (Like Timestamp columns), then there is
not really a shortcut per-se. You will either have to back up and restore,
or you can transfer it by having your PERL script query your system
catalogues, and build the query excluding the Timestamp column. queries to
system catalogues differ quite a bit between DBMS's, but if you do this sort
of thing quite a bit, then it is probably worth your time to build a PERL
script to build large transfer scripts like the one you are describing. You
didn't tell us what DBMS you are using, but just in case, the type of query
you would use in an MS SQL server would be:
SELECT name FROM syscolumns
WHERE id = object_id(<tablename>)
and type != 128
order by colorder
(--Going by memory on what to exclude...might not be right. Check docs to be
sure)
>From there, use your PERL to build those results into a $columnlist and
build this query:
INSERT INTO Target..Table ($columnlist)
SELECT $columnlist FROM Source..Table
Again, using the naming convention of Database..Table....use the naming
convention your DBMS uses.
Of course, if target and source cannot connect directly to each other, then
you have to use the PERL script to process each line individually, but you
can still use the query of system catalogues to generate the $columnlist and
save you from having to type out 150 column names.
Hope this helps.
Steve Howard
-----Original Message-----
From: Krung Saengpole [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 29, 2001 12:24 AM
To: [EMAIL PROTECTED]
Subject: Copy records from other database.
Hi all,
I want to transfer record from one table in first database to another table
in second database. Can I use DBI to do it w/o INSERT... statement? Because
of many fields of my table (around 150 fields), or can use INSERT statement
easily with some method?
Any helps would be gracefully thank.
Krung
