SQL Server 2000 import and export data

Data Import and Export means to import text files or external databases (access, Foxpro, Excel, etc) or convert the SQL Server database to another data format.
11.2.1 DTS
DTS is short for Data Transformation Service. It provides services to convert one data source to another. DTS provides many services, including data replication, data conversion, and notification Status.
1. Export data
[Example 11-11] export the xscj database to the Access 2000 database system and name it access_xscj.mdf.
Before exporting SQL server data, you must first create a blank database named access_xscj.mdf in Access 2000 to accept data from the SQL Server database.
(1) create a blank database named access_xscj.mdf in Access 2000
① Create a new database in Access 2000 and name it access_xscj
② In the database window, double-click the "Create Table with designer" option,
③ In the Table Designer window, set the table field name, data type, and other options.
④ Click Save and use the table name access_xsjbxxb and access_cjb until all the tables in the database access_xscj.mdb are created.
Note: The newly created access database can also be a blank database, so steps 2 to 5 can be omitted.
(2) Use the SQL server data import/export Wizard to export data from the SQL Server database to the access database.
① Start the data import and export tool. Select Start, program, Microsoft SQL Server, and import and export data. The DTS import/export wizard screen is displayed.
② Click Next. The Select data source dialog box is displayed. In the data source drop-down list box, select Microsoft ole db provider for SQL Server or Microsoft ODBC drive for SQL Server ].
③ In the server dialog box, enter or select the name of the SQL Server server and select the logon method of the SQL Server server. You can use Windows NT authentication, you can also select the SQL Server Authentication mode. If you select the next mode, you also need to enter the user account name used for Logon in the User Name text box, and then enter the logon password in the password box.
④ Click Refresh to bring all the databases on the selected server to the database drop-down list. Then, select the SQL Server database to be exported. In this example, select xscj database. If you need to set the process options of the source database ole db driver, click Advanced, set the options in the advanced Connection Properties dialog box, and then click Next.
⑤ Select the target database. When the Select destination dialog box appears, select the target database format from the destination drop-down list box. The list provides a variety of options, such as text files, access databases, Excel databases, and Visual FoxPro databases. Here, select Microsoft Access database format.
6. In the Object Name text box, enter the Object Name and path of the target database. In this example, the Object Name of the Access database is access_xscj.mdb ].
If you need to log on to the target database, enter the logon user name and password in the [user name] and [Password] text boxes. If you need to set the process options of the target database ole db driver, click the Advanced button, and then set related options in the advanced Connection Properties dialog box. Select the target database and complete related settings, and click Next.
7. Copy the entire table or part of the data. In the dialog box, select one of the following options:
L to copy the source table or view in the selected database to the target database, select copy table and view from the source database.
L if you only want to use a query to copy the specified data to the target database, select [use a query to specify the data to be transmitted.
In this example, select the first item and click Next.
⑧ Select the source table. The dialog box lists the tables contained in the source database. You can select one or more tables as the source table. Select a copy box in the source column. After you select a source table, the name of the target table is displayed in the [PURPOSE] column. Select the source table and click Next.
⑨ Converts data and stores it as a data transmission package. When the "Save, schedule, and copy package" dialog box appears, select the "Execute now" check box in the "time" area to perform data conversion immediately after setting the data export options; in the Save area, select the save data transmission package check box and select the SQL server option to save the data transmission package to SQL Server. If you want to repeat this operation later, you only need to execute the DTS package. Click Next.
⑩ Save the DTS package. Because you have selected to save the DTS package in the previous operation, the "Save DTS" dialog box appears. In the figure, specify the name of the DTS package in the "name" text box, enter the description in the description box. You can also set the password for accessing the package and other options. If you know the user password, you can only execute the package and cannot modify the package. If you know the owner password, you can perform and modify the package. Then, click Next.
Complete the settings. At this point, the wizard has collected the parameters required for data conversion, which are listed in the picture. Because you have set to perform data conversion now, click Finish to start data conversion.
When the data conversion is complete, click OK and click Finish.
Through the preceding operations, tables in the SQL Server database are exported to the Access 2000 database. You can open and browse the table data in the ACCESS database,
During data export, a data transmission package is created. In the SQL Server Enterprise Manager, expand the data conversion service folder to view the relevant data transmission package information. If you want to perform a data conversion operation, right-click the data transmission package in the SQL Server Enterprise Manager, and select the "run package" command to run the data transmission package. The corresponding table is generated in Access 2000.
You can also import data sources such as Excel to the SQL Server database. This type of data conversion is often used in the early stage of the system. It imports previously prepared data such as access and Excel into the SQL Server database without re-entering the data.