Re: Script to copy table between databases
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 02/19/04
- Next message: burke: "intersect function"
- Previous message: Ray Higdon: "Re: How do I separate in SQL"
- In reply to: newbie: "Script to copy table between databases"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 19:26:19 +0530
hi newbie,
There isn't any built in tool available with sql server that will script the
data like "insert into....". If you want to do a data transfer between the
database you have various optios with you.
1) backup/restore of database
--above option will be useful to create a complete replica of the database
on to the destination database.
2) DTS.
--can be used to transfer selective objects from the database.
3) bcp/bulk insert.
With the help of bcp command you can transfer the data to and from the ascii
files. whereas with the help of "bulk insert" command you can only insert
data from ascii/flat files into the database. To create a complete
definition of the table structures you can take help of "Enterprise manager"
scripting option. Open the enterprise manager, right click on the database.
click on "all tasks" . click on "generate sql script". click on "show all".
Select the objects you wish to script (all tables/all sps etc.). this will
generate a script file with DDL commands. Run this file on the second
database where you want to generate similar database structure, and later
you can transfer the data from the ascii files that has been generated with
the help of BCP command.
4) But above all i think you can make use of "INSERT INTO...SELECT "
statement becase as you stated "destination table already exists and is
identical in design".
So your syntax will look something like this.
insert into db_name.owner.<destination_table> (col1,col2) select col1,col2
from db_name.owner.<source_table>
I understand from your statement "I need to overwrite the destination table
data if it already exists" that, you want to empty the destination table
which, can be accomplished using:
"TRUNCATE TABLE <table>" or "DELETE FROM <table>" command. if you want to
completely empty the table use TRUNCATE TABLE command which is faster.
-- Vishal Parkar vgparkar@yahoo.co.in
- Next message: burke: "intersect function"
- Previous message: Ray Higdon: "Re: How do I separate in SQL"
- In reply to: newbie: "Script to copy table between databases"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
