How to export data and tables from SQL Server and Oracle

How to export data and tables from SQL Server and Oracle

first. Export and import data to a database in SQL Server

1. Select the database to be transferred in the SQL Server Enterprise Manager, right-click and choose all tasks> back up the database.
2. Select database for backup-complete,
Target backup to press add
Enter a custom backup database file name (Suffix: BAK) under the hard disk of the SQL Server server)
Override select override existing media
Click OK.
If the generated backup database file is larger than 1 MB, compress it with a compression tool before transmission to the Internet.
3. Use FTP, Remote Desktop, or PCAnywhere
Upload the backup database file generated in step 2 or the compressed file to the target SQL Server database. If there is compression, decompress the file.
4. If the target SQL Server database does not exist, create a new database first;
Select the newly created database, right-click it, and select all tasks-> Restore database
Restore-> from device-> select device-> disk-> Add (find the name of the backup database file to be imported)-> OK
Restore backup set-> database-complete
Click OK. The database has been imported successfully.
(If you restore a database on an existing SQL Server database, you may encounter another user using it and the restoration operation fails,
Choose "manage"> "current activity"> "Lock/object"> "find the lock process number in the Database"> use the kill process number in the query analyzer to kill the lock, and then restore)
Note: If you back up files from the original target SQL Server database (*. bak) Restoring the database will replace all existing tables, stored procedures, and other database objects with the contents of the latest imported backup database.
If you must restore the backup file (*. bak), you need to create a new database with the same logical Name and number of backup files (*. the logical names and quantity of databases in bak are consistent;
The name of the physical file of the new database must be different from the physical file of the database in the backup file (*. bak.

 second. Export and Import a table in SQL Server
1. No firewall. The same LAN may not be in the same LAN, but can access each other through the Internet.
Select the target database in the SQL Server Enterprise Manager, right-click the database, select all tasks-> import data-> pop-up data conversion service import/export wizard window-> next-> select data source-> data source (for SQL Server's Microfost OLE DB Provider) -> Server (you can select all SQL Server servers that can be accessed within the LAN, or directly enter an IP address) -> choose whether to use windows authentication or SQL Serve authentication (enter the database user name and password)-> database (you can select a database with all permissions on the SQL Server above) -> next-> select purpose-> purpose (for the SQL Server Microfost OLE DB Provider)-> Server (the default is the export Server selected in the previous step, you can also select all the SQL Server servers that can be accessed in other LAN, or directly enter the IP address)-> target database (you can select the database with all permissions on the SQL Server above) -> next-> Create a table copy or query-> select copy tables and views from the source database (you can also select a query item to specify the data to be transmitted) -> next-> select source table and view before the table and view to be imported-> the same table name appears for the target (you can manually change it to another table name) -> conversion-> In column ing and conversion, you can modify the correspondence between fields in the source table and the target table, modify the type and length of fields in the target table, and create a target table, add rows to the target table, remove and recreate the target table, enable flag insertion and other options-> OK-> next-> Save, schedule, and copy packages-> time-> Run now (if you want to automatically export the imported data at intervals of time, select the scheduling DTS package for later execution)-> Save (optional)-> [Save the DTS package (if you want to transfer the same batch of data in the future, you can save the exported content and steps and save them to SQL Server. Enter the package name and detailed description of DTS when saving the exported content.)-> next->]-> complete
Executing the package-> graphical interface showing the steps and status of creating a table and inserting a record-> complete
2. Go through the firewall, not in the same LAN
① Export the data in the table to a text file:
Select the target database in the SQL Server Enterprise Manager, right-click the database, select all tasks-> import data-> pop-up data conversion service import/export wizard window-> next-> select data source-> data source (for SQL Server's Microfost OLE DB Provider) -> Server (you can select all SQL Server servers that can be accessed within the LAN)-> choose windows authentication or SQL Serve authentication (enter the database user name and password) -> database (you can select a database with all permissions on the SQL Server selected above)-> next-> select destination-> destination (text file) -> file name (generate a custom text file in the hard drive of your computer) -> next-> Create a table copy or query-> select copy tables and views from the source database (you can also select a query item to specify the data to be transmitted) -> next-> select the target file format-> source (select the table to be exported) -> use the default delimiter-> select the option that contains the column name in the first package-> next-> Save, schedule, and copy the package-> time-> run immediately (if you want to implement a time interval automatically export to a text file, select the scheduling DTS package for later execution)-> Save (optional)-> [Save the DTS package (enter the DTS package name and detailed description when saving the package) -> next->]-> complete
Running package-> graphical display of the steps and status of table-to-text files-> complete
If the generated text file is larger than 1 MB, compress it with a compression tool before transmission to the Internet.
② Use FTP, remote desktop, or pcanywhere
The text files generated in step 1 or the compressed files are uploaded to the target SQL Server database. If any files are compressed, decompress them.
③ Import text files to the target SQL Server database
When you directly import a text file to a new table name with the same name as the text file in the target SQL Server database, all fields are converted into strings by default.
So we need to do this:
Create a table on the source SQL Server database
Choose SQL Server Query analyzer> select table name in the source database> right-click and choose Edit object script in the new window> Create> copy the created table name in the new window. execute the SQL statement for creating a table name in the analyzer of the target SQL Server database, generate an empty table structure. (If such a table name already exists, modify the SQL statement for table creation, and add the year and month of the import time after the table name, for example, table_0113)
Call the import/export tool-> pop-up data conversion service import/export wizard window-> next-> select data source-> data source (text file) -> file name (it has been uploaded to the text file to be imported in the target SQL Server database. The suffix can be *. TXT, but select all files that can be opened by the regular text editor) -> next-> Select File Format-> use the default delimiter-> select the first line containing the column name option-> next-> specify a column delimiter-> comma-> next-> select purpose-> purpose (for the SQL Server microfost OLE DB Provider) -> server (you can select all SQL Server servers that can be accessed in the target LAN)-> choose Windows authentication or SQL Serve authentication (enter the database user name and password) -> database (you can select a database with all permissions on the SQL Server server selected above) -> next-> select the source table and view-> modify the name of the target table as the name of the created table-> convert (append rows to the target table) -> next-> Save, schedule, and copy packages-> time-> Run now (if you want to automatically import text files at intervals, select the scheduling DTS package for later execution) -> Save (optional)-> [Save the data transmission package (enter the data transmission package name and detailed description when saving)-> next->]-> complete
Executing the package-> display the steps and status of text files to the table on the graphic interface-> complete
If you want to change the table name of the date and month of the import time, for example, from table_0113 to the original table name, change the original table name to table_old_0113 and table_0113 to table in the Enterprise Manager. This will interrupt the frequently accessed tables in the application.
Note: The index and primary key constraints on the source table cannot be transferred using the methods 1 and 2 described above. You also need to manually create the index and primary key.
The constraints of the Flag seed and not null can be inherited.
When importing a view, all the real data in the source view is imported into a new table instead of a view.

  third. SQL Server Stored Procedure or user-defined function Export and Import

1. Export stored procedures or user-defined functions to *. SQL files
Select the source database in the SQL Server Enterprise Manager, stored Procedure-> single-or multi-select stored procedure to be transferred-> User-Defined Function-> single-or multi-select function to be transferred-> right-click, select all tasks-> Generate SQL scripts-> OK-> Generate a custom * in the hard drive of your computer *. SQL file-> Save-> generating SQL script-> succeeded
2. If the target database goes through the firewall and is not in the same Lan, you must use FTP, remote desktop, or pcanywhere to generate Step 1 *. the SQL file is uploaded to the target SQL Server database Server.
3. Use the query analyzer to access the target database of SQL Server. Select a file from the menu and choose open> open to open the query file. Select the file generated in step 1 *. SQL file-> click execute query's green inverted triangle shortcut key-> the query window will show the executed message (sometimes because of the dependency between the stored procedure and the User-Defined Function), some errors will be reported.
It is best to first execute the *. SQL file of the User-Defined Function, and then execute the *. SQL file of the stored procedure)

  fourth. Import tables to the SQL Server database in the ORACLE database

1. Install ORACLE Client software or oracle odbc Driver on the target SQL Server database Server. Configure the ORACLE database alias (service name) in \ network \ admin \ tnsnames. ora ).
For specific configuration methods, refer to this site article: Considerations for connecting clients to servers
2. Choose Win2000 or win2003 Server> Management Tools> data source (ODBC)> system DSN (which can be used by NT domain users on this machine) -> Add-> Oracle ODBC driver-> complete-> data source name can be customized. I usually enter the SID mark of the Oracle database. You can enter the detailed description of the Oracle database in description, alternatively, you can enter the Oracle database alias defined in step 1-> OK without entering-> data source service name.
(The user's DSN and file DSN can be configured Similarly, but there are some restrictions in use)
3. in SQL Server's data import and export tool, choose data source> data source (Other (ODBC Data Source)> select the system DSN source name defined in ODBC in step 1, in the username and password field, enter the username and password of the Oracle System-> next-> select the target and select the SQL Server database (which is consistent with the above two points and will not be repeated ).
Note: It is important to 'convertible 'between an oracle table and an SQL Server table. You can change the default field data type, such as image-> text, decimal-> Int.

  fifth. Import tables to the ORACLE database in the SQL Server database

Method 1. for export purposes, select the Oracle database defined in the ODBC Data Source. Note that the table names in Oracle are in uppercase. I usually create a table structure on the Oracle side, and then select the SQL Server Source table to append data to the Oracle destination table. data transmission speed is slower than method 2.
Method 2: import data from the SQL Server database to the Oracle database. You can use Oracle9i Enterprise or Personal Edition database in Windows for data transfer.