SQL Server Export and Import Author: Yang Xiao (http://blog.sina.com.cn/u/1237288325) Because of the company's project requirements, the database is imported and exported between two databases. I have studied SQL Server Import and Export and found that some articles are more comprehensive. Now I want to correct them for your reference. 1. 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 restore the database from the backup file (*. Bak) on the original target SQL Server database
Replace all existing database objects such as tables and stored procedures with those in the backup database recently imported.
If you must restore part of the data in the backup file (*. Bak), You must create a new database,
The logical name and quantity are the same as the logical name and quantity of the database in the backup file (*. Bak;
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. Ii. 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. In the SQL Server Enterprise Manager, right-click the target database and choose all tasks> import data>
The data conversion service import/export wizard window appears-> next-> Select a data source> data source (provided by SQL Server's microfost OLE DBProgram)->
Server (you can select all SQL Server servers that can be accessed in 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 server selected above)-> next-> Select purpose-> purpose (the microfost ole db provider for SQL Server)->
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 (select the database with all permissions on the SQL Server server selected above)-> next-> Create a table copy or query-> copy the table and view from the source database (you can also select a query to specify the data to be transmitted)-> next->
Select source table and view-> select Source 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, and modify the type and length of fields in the target table,
You can also choose to create a target table, Add rows to the target table, remove and re-create the target table, and enable flag insertion and other options-> OK-> next-> Save, schedule, and copy packages-> time-> Run now (if you want to automatically export and import data at intervals, select the scheduling DTS package for later execution)->
Save (optional)-> [Save the DTS package (if you want to transfer this batch of identical data later, you can save the imported content and steps for this export,
Save the package to SQL Server. Enter the package name and detailed description of DTs when saving the package.)-> 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:
In the SQL Server Enterprise Manager, right-click the target database and select all tasks.
Import data-> the data conversion service import/export wizard window appears-> next-> Select a data source-> data source (the microfost ole db provider for SQL Server)->
Server (select all SQL Server servers that can be accessed in the LAN)->
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 server selected above)-> next-> Select purpose-> purpose (Text File)-> file name (generate a custom text file on your computer's hard drive)-> next-> Create a table copy or query-> copy the table and view from the source database (you can also select a query 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 with column name in the first package-> next-> Save, schedule, and copy packages-> time-> Run now (if you want to automatically export data to a text file at intervals, select the scheduling DTS package for later execution)->
Save (optional)-> [Save the DTS package (enter the DTS package name and detailed description when saving)-> 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. // Yangxiao ② 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 source database> right-click and choose Edit object script in new window> Create>
Copy the SQL statement used to create a table name in the new window Execute the SQL statement for creating the table name in the query analyzer on the target SQL Server database to 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-> the data conversion service import/export wizard window is displayed-> next-> Select data source> data source (Text File)>
File Name (the text file that has been uploaded to the target SQL Server database to be imported. The suffix can be *. txt,
But select all files that can be opened in the regular text editor.)-> next-> Select File Format-> use the default delimiter-> select the option with column name in the first line-> next->
Create a column delimiter-> comma-> next-> Select purpose-> purpose (the microfost ole db provider for SQL Server)->
Server (select all SQL Server servers that can be accessed in the target LAN)->
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 server selected above)-> next-> Select the source table and view, and modify the name of the target table to the name of the table you just created. Select convert (append a row to the target table) and click 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 DTS package (enter the DTS 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 year and month of the import time, for example, from table_0113 to the original table name,
In the Enterprise Manager, change the original table name to table_old_0113 and table_0113 to table.
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. Iii. 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-> select one or more stored procedures to be transferred->
User-defined functions-> select one or multiple functions to be transferred->
Right-click all tasks and choose generate SQL scripts. Click OK to generate a custom *. SQL file on the hard drive of your computer.
Save-> generating SQL script-> succeeded 2. If the target database goes through the firewall and is not in the same LAN,
You must upload the *. SQL file generated in step 1 to the target SQL Server database server by using FTP, Remote Desktop, or pcAnywhere. 3. Use the query analyzer to access the target SQL Server database,
Select a file from the menu-> open the query file-> select the *. SQL file generated in step 1-> click the green inverted triangle shortcut key for query execution->
The execution message appears in the query window (sometimes some errors may be reported because the stored procedure and user-defined functions are dependent on each other.
It is best to first execute the *. SQL file of the User-Defined Function, and then execute the *. SQL file of the stored procedure) 4. 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 $ ORACLE_HOME \ Network \ admin \ tnsnames. ora ). 2. Choose Win2000 or win2003 Server> Management Tools> data source (ODBC)> System DSN (this machine can be used by NT domain users)-> Add-> Oracle ODBC driver-> complete-> Data source name can be customized. I usually fill in the SID mark of the Oracle database,
You can enter the detailed description of the Oracle database in description, or you can leave it blank-> Enter the Oracle database alias defined in step 1st for data source service name-> OK. (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. Enter the username and password of the Oracle System in the username and password field.->
Next step-> select the target and select the SQL Server database (which is consistent with the one mentioned in the second point above, 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. V. Import tables to the Oracle database in the SQL Server database Method 1: select the Oracle database defined in the ODBC data source for export purpose. 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. Note: When Oracle accesses the database link of SQL Server, the field name is caused by double quotation marks when using select. About Author:
YANG Xiao, master of computer science, worked in Java Development in NEC and softbrain.
Blog: http://blog.sina.com.cn/u/1237288325
|
