24. Load the database
Load operation performance
A large number of copy tools
Bulk INSERT statement
Data conversion service
Staging data sheet
SELECT ... INTO statement
Summary of this chapter
Learn to use the database and the database data sheet to learn to load the information. There are several ways to load the data into the database. What ways use depending on the source of information, what processing is processed, and where information will be loaded. This chapter will learn how to load the following load database:
BULK COPY PROGRAM, BCP: BCP is an external program provided by Microsoft SQL Server 2000, which is easy to load the data file to the database, and BCP can also copy data from data sheet in SQL Server to the information file. . Using BULK INSERT instructions: Bulk INSERT T-SQL instructions can be replicated from the data file to the SQL Server data sheet. Since this instruction is SQL statement (executed in ISQL, OSQL, or Query Analyzer), the process will be executed as the execution of SQL Server. This instruction cannot be replicated from SQL Server to the information file.
Data Transformation Services (DTS): DTS is a set of toolbars provided by SQL Server to facilitate copy information to SQL Server, or copy data from SQL Server. DTS includes remitting and remitting information wizards.
Explanation Although the Staging data sheet does not provide the method of loading the information, it is generally used for the library load.
Each method has its own characteristics and properties to suit the user's different loading requirements.
Note that the recovery library from the backup file can also be considered as a form of data library, which will be discussed in Chapter 32 and 33.
Some of the database parameters sets of BCP or BULK INSERT are common, and the database parameters define how the BCP is executed, so the settings of the parameters must be completed before the load operation is performed. The following methods are also very practical: SELECT ... INTO statement: used to copy data to another. Staging Data Sheet: For the temporary storage table, usually used to convert the information in the database, making the data load process more smoothly and can be modified during the load process. The performance of load operation This section describes three set options that are commonly used to enhance the performance of load operation. Two of these options affect transaction record files when performing a large number of replication operations, and the other affects the lock. Use a large number of replicated methods to replicate a lot of information at a time, it can be considered as the most efficient method of reconstructing a data. Transaction Record Section Options SQL Server uses a complex transaction record file mechanism to determine that the information will not lose due to system failure. Transaction record files are necessary for the integrity of information in the system, but may increase system burden. The system's burden can be reduced by reducing the amount of transaction data when loading a large amount of load.
Note After the system failure, SQL Server will restore the database. The transaction that has not been recognized when the fault is faulty will be restored (canceled); the transaction that has been approved when the fault is faulty, and the Roll Forward is returned. Rehabilitation or forward recovery can restore the system to the state before the fault occurs. Backup and recovery will be discussed in Chapter 32 and 33.
In the preset state, all database insertions will be completely recorded in order to restore or restore insertions during the system failure. By deactivating a lot of data (using BCP, Bulk Insert), you can reduce the amount of recorded data, but also allow the system to allow the restoration operation (approved transaction Disappeared). This option enables a large number of replication performance, but because the record used for forward recovery is not recorded, the data library load must be restarted when the system is problematic. When satisfying the following conditions, a full record of a large amount of load operation will be deactivated: Database Options Select INTO / BULKCOPY is set to true, listed as syntax using sp_dboption pre-preserved program: Exec sp_dboption data_name, "select into / bulkcopy", True
You can also set this option using Enterprise Manager. Chapter 8 has a detailed introduction to Enterprise Manager. The target data sheet loaded is not set to be replied (Subwritten in Chapter 26, the twenty-seventh chapter, and the twenty-eighth chapter.). Tablock prompt already specified (relevant information about prompts can be referred to this chapter
Note that you should only establish a database, and if you load a lot of data into the demand of the database, Trunc.log On Chkpt is set to True, and the record of the transaction record file will affect the entire database and will cause the system. Unable to restore when a problem occurs. Therefore, in the case of normal operation, if the project is important to the system, this option should not be used. If Trunc.log On Chkpt has been set to true, you have to remember to set the option to false after completing the load operation.
Trunc.log on chkpt can be set by sp_dboption, the syntax is as follows:
Exec sp_dboptionDatabase_name, "trunc. log on chkpt", TRUE
Note You can set more options in the option tab in the Database Properties window, as shown in Figure 24-1. Restrict access can limit the access library for a specific role or a single user; only read settings are not allowed to write to the database. Preset to ANSI NULL Specify whether the data line of the database is defined as NULL or NOT NULL; repeated trigger programs allow trigger programs to be triggered; automatic update statistics allows SQL Server to optimize The process automatically reconstructs expired statistics; damaged page detection can detect incomplete paging; automatically turn off the specified database will be released after its resource is released and all users are shut down; automatic compression Specify SQL Server will periodically Compressed library file; automatic statistics to specify any lost statistics required for query optimization will be automatically established during optimization; use the quotation number identification item to select this option to specify that the double quotes can only be used for identification, for example The data line and the data sheet name, and the character string must be enclosed in single quotes.
Lock Options Use the Table Lock on Bulk Load option to improve a large number of replication performance, in a large number of replication operation mode, this option is locked in a single data sheet to replace multiple data columns to lock. Use sp_tableoption to set the Table Lock on bulk load, the settings of the parameters are as follows: Exec sp_tableoption "Table_name", "Table Lock on Bulk Load", TRUE
After the load is complete, remember to reset the parameters of Trunc.log on Chkpt. Since the Table Lock on Bulk Load option only affects the lock mode of the data sheet, it does not reduce the performance efficiency when you do not perform a lot of replication.
Figure 24-1 Database "Options" tab of the "Properties" window
Description To get the performance performance of the Table Lock on Bulk Load option, you must use the TabLock setting.
A large number of replicated tools use BCP to copy information from SQL Server from a data file, or from SQL Server replication information to the information file. To transfer information from other databases to SQL Server, BCP is also very practical. This section learns how to use BCP and its options, and learn how to format the data so that the data will be used to exchange data into SQL Server or from SQL Server. The BCP syntax BCP is an execution program of the instruction line, using BCP to require a specific parameter, and BCP provides many selective parameters for you. The format of the BCP instruction is as follows:
BCP {[[Database_name.] [Owner].] {Table_name | View_name} | "query"}
{IN | OUT | queryout | format} Data_File
[-MMAX_ERRORS] [-FFORMAT_FILE] [-eerror_file]
[-Ffirst_row] [-llast_row] [-bbatch_size]
[-n] [-C] [-W] [-N] [-V (60 | 65 | 70)] [-6]
[-Q] [-ccode_page] [-tfield_term] [-RROW_TERM]
[-IINPUT_FILE] [-OOUTPUT_FILE] [-apacket_size]
[-Sserver_name [/ instance_name]] [-ulogin_id] [-ppassword]
[-T] [-v] [-r] [-k] [-e] [-h "hint [, ... n]"]
The necessary parameters The necessary parameters specify the capture and insertion of the data. As mentioned earlier, use BCP to capture and insert information into a SQL Server data sheet or view table from a data file, or insert information into the information file from a data sheet (or view table). In a large number of data sheets or view tables used in a large number of replications, the following two ways can be specified: the first method is the name of the data sheet or the visual table using the table / view_definition parameter. If the previously demonstrated instructions, the name of the database can be specified or the database where the table is located, or the owner of the specified data sheet or the view table. If the database name, the data sheet, or the checklist is located is the preset repository defined when the user logins, the 34th chapter will discuss the user definition in detail. Another method is to use a query specified data sheet or a checklist, using this method to specify information to be captured in the data sheet or viewing table. (Table / View_Definition parameters can be used to specify data sheets or view tables for capturing or inserting materials.) Query must add double quotes, you can add or do not add the Select statement of the Order By clause. When specifying the query definition, you must specify the queryout parameter (discussed in Table 24-1). The location of the data file in a large number of replication operations can be specified by the data_file parameter, but the specified path must be a valid path. Finally, you can specify one or more columns to specify a large number of copied parameters at the parameter table 24-1 of Table 24-1.
Parameter Description in the data sheet or view table of a large number of data from the file clip to the SQL Server library. OUT captures and a large number of replication materials from the SQL Server data sheet or a large number of copies into the archive. Queryout Specifies that when querying a large number of copying materials, the information defined from the SQL Server database will be obtained from the SQL Server Database. After the data is selected, it will be copied to the archive clip when the query is selected. FormatBCP creates a new format file to perform a large number of replication jobs, the specified formatting option (-n, -c, -w, -6 or -n) and data sheets or checklists can be used to establish formatting files. If the format parameter is used, the -f option must also specify at the same time. The format file can let you save the BCP definition, and then you do not need to repeat the definition when you use BCP again.
Selective Use Parameters You can choose to use the parameters column 24-2 to modify the BCP to perform a large amount of replication.
Table 24-2 Specify a large number of copied selective parameters
Parameter Description -a packet_size Specifies the number of bit groups transmitted by the network packet between the client and the servo terminal. -b batch_size Specifies the number of data copied by each batch operation, each batch operation is considered a transaction. Depending on the preset value, all data columns in the specified data file will be copied as the same batch operation, so if you perform a large amount of insertion information, this option may be specified so that each batch operation is done when the operation is complete. The data sheet is locked, which can perform other processing programs (that is, the time to lock the resource lock). -c Specifies BCP to perform a large number of replication jobs using CHAR character data type. -e err_file Specifies the path to the BCP error file. -f format_file Specifies the path to the format file used by the BCP. If the BCP is executed, use the FORMAT option, it will establish the Format File mentioned before. If you use this format, you don't need to use other format options. -h "hint [, ... n]" Specifies the prompt used in large quantities, the following is the optional prompt: Order (Column [ASC | DESC]) specifies the data line in the data line. Rows_per_batch = Number Specifies the number of data columns for each batch operation and -b, but should not be used with -b. The -b option treats the batch operation specified by the data column as a single transaction, transferred to SQL Server. When -b is not specified, the entire data file clip will be considered a single transaction, transferred to SQL Server, and use the ROWS_PER_BATCH to evaluate the measurement load size size, enabling a large amount of load optimization. Kilobytes_per_batch = Number Specifies approximately the number of thousands of packets (KB) per batch operation, this option and -b are similar, and the difference is that this option specifies the batch operation size in units of kilocacy group KB. Tablock specifies the locking of the information table level during a large number of copying loads. This tip can be greatly improved, because reduces the lock resource competition of the information sheet only keeps locking to reduce the list of information sheets during a large number of replication operations. Check_constraints Specifies a large number of copying of the conditional constraints. These condition constraints are ignored based on the preset value. -I input_file specifies the name of the reset file. In the response file, the instruction of each field is prompted to respond to the BCP issue when the BCP is executed using the interactive mode. -k Specifies the empty data to use the retained null value, not the preset value. -m max_errors Specifies the maximum number of errors that allow errors to occur before the end BCP operation cancels a large number of replication jobs. If this option is not included, the preset value is 10. -n Specifies BCP to use native data type. -o output_file specifies the output file that receives the BCP output. This output can be opened with a general text file, such as Microsoft's NOTEPAD or other tools, and more. -q Specifies that when the data sheet or view table name contains non-ANSI character (for example, blank keys), you must use the quotation marks to identify the double quotes. -r row_term Specifies the data column termination symbol, and the preset value is a new line character (/ N). -t field_term Specify field termination symbol (also known as separated symbol), the preset value is a TTAB. -v Print BCP version number and copyright information. -w Specifies that BCP performs a large number of replication jobs using the Unicode character. -C code_page Specifies the word code page (Code page) in the data file.
-E Specifies the value of the rendering identification Identity data line in the file that is replicated. -F first_row Specifies the starting first data column number of a large number of replication operations. If the data column number is not specified, the preset value of the first data column is 1. This option is quite suitable if you want to skip the title information of the information file. -L last_row Specifies a large number of replicated operations last ended data column numbers, and the preset value is 0 (0 indicates the end of the data is the last information column of the data file), indicating the last information column of the specified data file. If you just want to copy a specific number of information columns, this option is quite suitable. -N Specifies that BCP uses native data type for non-font data; for the native data of the Unicode character data, the NNIC data is used to perform a large number of replication jobs. -P password Specifies the password of the user account login to the identification code in the -U option. -R For the user-end system computer area set the defined area format, specify the currency, date, and time information used. -S server_name Specifies the server name to be used to load. -T Specifies the trust connection that can be used. After using this option, you don't need to use login_id and password variables; use the network user security credentials. -U login_id Specifies the user account login identification code used when connecting to SQL Server. -V 60 | 65 | 70 Specifies the use of the old version of SQL Server to perform a large number of replication jobs. Use this option to use the character -c or -n option. -6 Specify BCP Use SQL Server 6.0 or SQL Server 6.5 data type to perform a large number of replication jobs. When using BCP, you can specify a BCP's execution of the BCP using a single option or combined with several options, and obtain BCP powerful advantages. We will use these options through example learning. Using BCP We will learn to use BCP to load or output SQL Server in several examples. These examples do not include all BCP scope of use, but sufficient to demonstrate several modes and methods available to the BCP. You can use the previously described instruction line parameters or use BCP in an interactive manner. To use BCP in non-interactive mode, you must specify -n, -c, -w or -n parameters. If these parameters are not specified, the BCP will execute in the interactive mode.
Note All Examples will use the Customers data table in the Northwind database.
Use BCP Interactive Mode to load data Use BCP loading data in interactive mode to have a certain difficulty, because the data line length and data type should be specified. When you use the instruction line option, you don't need to specify these information, the description will be discussed in the next section. Using BCP loading information in interactive mode is not a good method, the following will demonstrate how BCP works in interactive mode, in example, we will copy data in the Data2.file profile to the Northwind database Customers information In the table, and write the error in the Err.file file. You must first create Data2.file, which contains information to be loaded. To start an interactive mode using the system administrator, enter the following instructions:
BCP Northwind.dbo.customers in data2.file -e err.file -usa
The translation note if an error message that cannot be connected to SQL Server may be that you do not specify the execution of the SQL Server, specify the SQL Server installed on your machine using the -s option.
Next, a prompt will be prompted to enter a password, please enter the password of the System Administrator (SA). BCP will then provide you with information to copy information, the following is an example of the interaction:
Please enter the file storage type of Customerid [nchar]: char
Please enter the front length of Customerid [1]: 0 Please enter the field length Customerid [26]: 5
Please enter the end symbol [NONE]:
Please enter the file in the field CompanyName [nvarchar], file storage type: char
Please enter the front length of the column CompanyName [1]: 0
Please enter the field length CompanyName [189]: 40
Please enter the end symbol [NONE]:
Please enter the file with the file containing the file storage Type: char
Please enter the front length of ContactName [1]: 0
Please enter the field length ContactName [143]: 30
Please enter the end symbol [NONE]:
Please enter the file with the file containing the file storage Type: char
Please enter the front length of ContactTitle [1]: 0
Please enter the field length ContactTitle [143]: 30
Please enter the end symbol [NONE]:
Please enter the file type address [nvarchar], save the type: char
Please enter the front length of Address [1]: 0
Please enter the field length Address [283]: 60
Please enter the end symbol [NONE]:
Please enter the file storage type of City [nvarchar]: char
Please enter the pre-length length of CITY [1]: 0
Please enter the field length city [73]: 15
Please enter the end symbol [NONE]:
Please enter the file in the field region [nvarchar], save the type: char
Please enter the front length of the REGON region [1]: 0
Please enter the field length Region [73]: 15
Please enter the end symbol [NONE]:
Please enter the file store Postalcode [NVARCHAR] to save the type: char
Please enter the front length of Postalcode [1]: 0
Please enter the field length Postalcode [49]: 10
Please enter the end symbol [NONE]:
Please enter the file storage type of the field country [nvarchar]: char
Please enter the pre-length length of Country [1]: 0
Please enter the field length country [73]: 15
Please enter the end symbol [NONE]:
Please enter the file of the file Phone [nvarchar] storage Type: char
Please enter the front length of the field Phone [1]: 0
Please enter the length of the PHONE [115]: 24
Please enter the end symbol [NONE]:
Please enter the file FAX [NVARCHAR] to save the type: char
Please enter the front length of FAX [1]: 0
Please enter the field length FAX [115]: 24
Please enter the end symbol [NONE]:
Do you want this format information into a file? [Y / n]: Y
Main file name [bcp.fmt]: data.fmt
Start copying ...
SQLSTATE = S1000, NativeError = 0
Error = [Microsoft] [ODBC SQL Server Driver] is incorporated in BCP DATA-FILE
EOF
5 data columns have been replicated.
Online package size (bit group): 4096 Pillime (microseconds): Total 471
You must understand the above values before you start. Since there is no number of copied data columns, the error message will appear when the BCP executes the end of the archive. When using BCP and instruction line selection items When using BCP to load data, use the instruction line option is a simple method. The example used in this section will use BCP to load information from a data file, which uses Tab as a field Separate symbols, and the field type is a CHAR data type. We will specify the information in the data file as a char format in the data file. The use -c option also allows BCP to execute in non-interactive mode. The following instructions can be copied into the Customers data sheet in the Data.file profile of 25 lines.
BCP Northwind.dbo.customers in data.file -e err.file -c -usa
Since the -c option specifies the CHAR data type, no need to set the field length or the front length. Suppose we establish 25 columns in data.file, and the TAB-vary-separated data and the data lines in the Customers data sheet correspond to the SA password. This work phase will be as follows (Liabing the package size) And the time pulse may vary):
Start copying ...
25 data columns have been copied.
Network package size (bit group): 4096
Time pulse time (microsecond): Total 30
In this example, the direction of replication is set to IN, which means that the information is loaded into the database. It is recommended that you specify an error file because the error file can record any errors generated during BCP execution. If you do not specify an error file, the error will be sent to the screen. Using the Format option Load Information In
BCP Northwind.dbo.customers in data2.file -e err.fil -f data.fmt -l
5-elsa
Assuming that the password has been entered, and Data2.file is established, you will see as shown below:
Start copying ...
5 data columns have been replicated.
Network package size (bit group): 4096
Time (microsecond): Total 50
In addition to using the -f option, the example also contains the -l option, the -l option indicates the last data column replicated from the input file. In this example, the -l option specifies the 5th data listed as the last copied data column, so the EOF error message you have seen before. Using BCP Interactive Mode Capture Information Use BCP in the interactive mode to capture information from the database, which will be simply simply copied to the database. The reason is that when you get information, BCP will help you fill in the field length. Try now using the following instructions to copy data from the Customers table.
BCP Northwind.dbo.customers out DataOut.dat - E Err.File -usa
After entering this instruction and the SA password, the following work phase is performed:
Please enter the file storage type of Customerid [nchar]: char
Please enter the front length of Customerid [1]: 0
Please enter the field length CustomerId [26]:
Please enter the field end symbol [NONE]:
Please enter the front length of the column CompanyName [1]: 0
Please enter the field length CompanyName [189]:
Please enter the end symbol [NONE]:
Please enter the file with the file containing the file storage Type: char
Please enter the front length of ContactName [1]: 0
Please enter the field length ContactName [143]:
Please enter the end symbol [NONE]:
Please enter the file with the file containing the file storage Type: char
Please enter the front length of ContactTitle [1]: 0
Please enter the field length ContactTitle [143]:
Please enter the end symbol [NONE]:
Please enter the file type address [nvarchar], save the type: char
Please enter the front length of Address [1]: 0
Please enter the field length Address [283]:
Please enter the end symbol [NONE]:
Please enter the file storage type of City [nvarchar]: char
Please enter the pre-length length of CITY [1]: 0
Please enter the length of the field "[73]:
Please enter the end symbol [NONE]:
Please enter the file in the field region [nvarchar], save the type: char
Please enter the front length of the REGON region [1]: 0
Please enter the field length region [73]:
Please enter the end symbol [NONE]:
Please enter the file store Postalcode [NVARCHAR] to save the type: char
Please enter the front length of Postalcode [1]: 0
Please enter the length of the POSTALCODE [49]:
Please enter the end symbol [NONE]:
Please enter the file storage type of the field country [nvarchar]: char
Please enter the pre-length length of Country [1]: 0
Please enter the field length country [73]:
Please enter the end symbol [NONE]:
Please enter the file of the file Phone [nvarchar] storage Type: char
Please enter the front length of the field Phone [1]: 0
Please enter the field length phone [115]:
Please enter the end symbol [NONE]:
Please enter the file FAX [NVARCHAR] to save the type: char
Please enter the front length of FAX [1]: 0
Please enter the field length fax [115]:
Please enter the end symbol [NONE]:
Do you want this format information into a file? [Y / n] n
Main file name [bcp.fmt]:
Start copying ...
96 data columns have been copied.
Network package size (bit group): 4096
Time pulse time (microsecond): Total 20
This interactive BCP work phase has established a file with a teasing partial segmentation. The file is a text file, all the information is a character data type, and the notepad is reviewed. However, BCP did not add a wrap character at the end of each line, so when you use Notepad to review the information, the information guild is very long. Use BCP and selective option to capture information To create a profile to read and add a wrap character, you can use the -c option: bcp northwind.dbo.customers out dataout.dat -e err.file -c-el
After the input instruction is password password, the work phase is executed as follows:
Start copying ...
96 data columns have been copied.
Network package size (bit group): 4096
Time pulse time (microsecond): Total 10
Use the queryout option to capture the information. Let's take a look at the example of using the Queryout option. This option can specify the query when copying the SQL Server library data. When specifies the query, select a specific information, and only copy is selected. data of. Queryout is very simple, as long as the query is included in the dual quotation mark, the method is as follows:
BCP "Select Customerid, CompanyName from Northwind..customers"
Queryout DataOut.dat -e Err.file -c -usa
After entering the SA password, the working phase is shown below:
Start copying ...
96 data columns have been copied.
Network package size (bit group): 4096
Time pulse time (microsecond): Total 1
The output of this query is separated by Tab character, and each column has a data file with termination symbols. The two columns of this file are: Customen ID and CompanyName. This method is very useful when a specific data line or information column is captured in the database. Bulk INSERT State T-SQL Bulk INSERT statement and BCP are similar, both of which can replicate information in the data file into the SQL Server database. However, BCP can be used to copy data or to take information from SQL Server, but Bulk INSERT can only be used for data loading. This limit reduces the functionality of Bulk INSERT, but because the statement can be performed in the SQL Server to perform an external program, the data transfer step is removed, and the execution performance of the data is improved. Also make it more efficient than BULK INSERT statements than BCP. Bulk INSERT syntax and BCP, BULK INSERT states must set several necessary parameters and several selective parameters. You can use the following instructions to call Bulk INSERT (using isql, osql or query analysis) in SQL Server, which contains the necessary and selective parameters:
Bulk INSERT [['Database_name'.] ['Owner'].]
{'Table_name' from 'Data_File'}
[With
[BatchSize [= Batch_size]]]]]
[[,] Check_constraints]
[[,] Codepage [= 'ACP' | 'OEM' | 'RAW' | 'CODE_PAGE']] [[,] DataFileType [= {'char' | 'native "|
'widechar' | 'widenative'}]]]]
[[,] Fieldterminator [= 'Field_Terminator']]]
[[,] Firstrow [= first_ROW]]
[[,] FiRetriggers [= fire_triggers]]]]
[[,] Formatfile = 'format_file_path']
[[,] Keepidentity]
[[,] Keepnulls]
[[,] Kilobytes_per_batch [= kilobytes_per_batch]]]
[,] Lastrow [= last_row]]]
[[,] MaxerRors [= max_errors]]]]
[[,] ORDER ({Column [ASC | DESC]} [, ... n])]
[[,] Rows_per_batch [= rows_per_batch]]]
【, ]TERMINATOR [= 'Row_Terminator']]]]]
[[,] Tablock]
)]]
The location of the necessary parameter data file can be specified by the DATA_FILE parameter, but the specified path must be a valid path. The data insertion of the data is determined by the data sheet definition or viewing table. As mentioned earlier, you can specify a database name or a list of data sheets or view tables. If you try to insert the data inserted a visual table with the BULK INSERT instruction, you can only affect one of the bottom information tables in the checklist. Selective Use parameters You can selectively use the parameters and keywords column of Table 24-3 to modify the execution of Bulk INSERT, which is similar to the BCP options.
Table 24-3 BULK INSERT Selective Use Parameters
Selective use parameters Description BatchSize = size Specifies the number of columns in each batch operation, each batch operation is considered a transaction. Check_constraints Specifies the check condition constraint, depending on the preset value, these restrictions are ignored. CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'CODE_PAGE'] Specifies the word code page in the data file, only when the information contains char, varchar, or text data, CodePage is used. DataFileType [= 'char' | 'Native' | 'WideChar' | 'Widenative'] Specifies the data type in the data file, the type of preset value is char, and the other options also include Native (native data type), Widenative (Unicode) and Widenative (same as Native native data are different, the difference is stored in cHar, varchar, and text is stored in Unicode). Fieldterminator [= field_term] Specifies the paramount termination symbol terminal to be used in the char and WideChar data type, the preset value is / t (Tab). First data column numbers of firstrow [= first_ROW], the preset value is 1, indicating the first data column in the specified data file. This parameter can be used if you want to skip the title information in the information file. FormatFile [= format_file] Specifies the path to the format file. KeepIdentity specifies that the value of the Identity identification data is reserved in the message file. Keepnulls Specifies the null value of the null data. Kilobytes_per_batch [= number] Specifies the number of approximately information of each batch operation when a large number of copies. Lastrow [= last_row] Specifies a large number of replication, the last data column number inserted. The preset value is 0, indicating the last information column of the specified data file. You can use this option if you just want to copy a specific information column. MaxErrors [= max_errors] specifies that the maximum number of errors is allowed to occur before the end of a large number of copying insertion, and the preset value is 10. Order (Column [ASC | DESC]) Specifies the sorting method in the data file. Rows_per_batch [= rows_per_batch]] Specifies the number of information columns for each batch operation, each batch will be considered a single transaction copy. According to the pre-value, all data columns in the data file clip will be considered a single batch of operation insertion, perform an approved. When you perform a lot of insertion, you can consider specifying this option to unlock the data table lock when performing a batch operation, allowing other handler steps. Rowterminator [= row_term] To use the information column termination symbol terminal with the WideChar data type, the preset value is a new row font (/ n). Using BULK INSERT now to see two examples of BULK INSERT statements, in these two examples, we will load information from Data.File to the custom of the Northwind repository.
Note that Bulk Insert statements can only be used to load data into the database and are not available to capture information. Since the operation mode of Bulk Insert is not as much as BCP, the example here will be relatively small.
Use the following T-SQL statement, load data into the database: Bulk Insert Northwind..customers from 'c: /data.file'
WITH
(
DataFileType = 'char'
)
Go
You can join more options as needed, the following example uses more optional parameters:
Bulk INSERT Northwind..customers from 'c: /data.file'
WITH
(
Batchsize = 5,
Check_constraints,
DataFiletype = 'char',
FIELDTERMINATOR = '/ t',
FIRSTROW = 5,
Lastrow = 20,
Tablock
)
This statement will only be loaded from the data file into the 5th to 20th data column. No matter whether it is a preset value, the field termination symbol should be specified as TAB. This example also detects the limit conditions during a large number of insertions and locks the load-bearing transactions as a batch unit. Data Transformation Services (DTS) is part of SQL Server Enterprise Manager, allowing you to easily return your information to your database or turn your information from the database. DTS consists of two eiversia, including remittance wizards and emission. This section will discuss how to use these two eiversia. Imports into the wizard you can use the information to enter the wizard to enter the data library. In stock, BCP and BULK INSERT statements are different, and the elf can be exchanged in addition to other sources other than the information archive, the following is the step of using the elf:
In Enterprise Manager, an server group is expanded and a server that wants to enter the data. From the tool menu, select the wizard, then expand the data conversion service folder, select DTS to enter the wizard, press to determine. Data conversion services will appear - into / out the wizard screen, as shown in Figure 24-2.
Figure 24-2 "Data Conversion Service - Infant / Exchange Elf"
Press Next to display the selection data source screen, as shown in Figure 24-3.
Figure 24-3 "Select Source" screen
Here you can select the source from the drop-down list. We have chosen the text file option in Figure 24-3, you can also choose from the following information source options:
DBase Microsoft Access Microsoft Data Link Microsoft Excel Microsoft Visual FoxPro Other (ODBC Data Source) Other OLE_DB DATA SOURCE PARADOX DATA Files
The decision of the option is part of the ODBC driver installed when you install the system, for example, if the Oracle ODBC driver is installed, this option will be listed, and the information source screen will be in accordance with you. The selected information source changes, no matter which information is selected, you need to enter files or login information. Press Next to display the selection archive format screen, as shown in Figure 24-4. This screen will only appear when you choose Text File, which allows you to select a file format. Other options in the screen are as follows:
Use the separated symbol or use the fixed field to allow you to select the format of the file, and the specific separator or field width. File type drop-down list allows you to specify an email format to ANSI, OEM, or Unicode. Data Column Separation Symbol Down List Allows you to specify what symbols use in the file to end each of the data columns. Text positioning item drop-down list To specify which numeral mark to be used to secure the text file. The starting list specifies the number of data column numbers that start replication (from the beginning of the file 0). The first column has the first list of the data line name to specify the text file as the data line title instead of information. Figure 24-4 "Select Archive Format" screen
Select the separator to separate the symbols in {cr} {lf} as the data column, and set the text positioning item to
Figure 24-5 "Specified Division Division Symbol" screen
After you select the separated symbol, press the next step to enter the selection destination screen, as shown in Figure 24-6, you can select the data library to enter the data below, you must specify the SQL Server ODBC destination ( The ODBC alias of the database), the server and the database. In this example we specify the Northwind library, select whether to use Windows or SQL Server authentication in this screen. If you use SQL Server authentication, you must enter the SQL Server user name and password in the space, and SQL Server security will be described in detail in Chapter 34. If the user's name or password is invalid, you can press the rearrange button to try again. To change other attributes, such as security options, connecting out more, press the advancement button to modify, but these properties usually do not need to be changed. Press Next to display the selection source data sheet and see the watch screen, as shown in Figure 24-7. In this picture, you can call out the drop-on list from the destination field in the information sheet and the inspection table, select the information table to be exchanged. Press the preview button here to preview the information. All options and all options allow you to choose all the information sheets or you don't choose.
Figure 24-6 "Select Destination" screen
Figure 24-7 "Selection Source Data Sheet and View" screen
In the same screen, you can access the conversion service, which allows the data to be converted (change the data line, etc.) when the information is entered. To perform this project, press the conversion button (the button of three points under the conversion title), enter the data line correspondence and the conversion dialogue, as shown in Figure 24-8. In the Data Line Corresponding tab, you can select a destination data sheet, delete the information list or data column in the target table, and add data to the destination information table, preset to the additional information to the destination information table. If you choose to establish a target table, you can use the Edit SQL button to view and modify the SQL statement used to build a table.
Figure 24-8 "Data Live Correspondence" tab within the dialogue of the "Data Walk,"
Select the conversion tab view conversion option, as shown in Figure 24-9. In this tab, you can choose to copy the source data line to your destination information line or perform information conversion when copying to the destination. The accuracy conversion of the conversion service can be specified here (e.g., from 16-Bit to 32-bit; from 32-Bit to 16-bit). The conversion of NULL values can also be performed here (such as from NOT NULL to NULL; from NULL to NOT NULL). Figure 24-9 "Transfer" tab within the dialogue of "Data Walk and Conversion"
Press to determine the end dialogue, follow the next step to enter the storage, scheduling, and copy package (as shown in Figure 24-10), this screen can determine if it is entered now, or to arrange an emission schedule . Here you can also choose to store the DTS package for later execution of this remission. To select this project, press to store the DTS package check box (displayed in the stored area below), after selecting all the previously selected conversion service settings. Here we choose immediately. Press the next step to enter the DTS return / propalency wizard (shown in Figure 24-11), press the completion of the implementation.
Figure 24-10 "Storage, Scheduling and Copy Package" screen
Figure 24-11 "DTS Remission / Exchange Elf"
After the completion is completed, the execution of the package screen (as shown in Figure 24-12), a message will appear after the execution is completed, and inform you to enter the completion or generate errors.
Figure 24-12 "Package" screen
The use of DTS is easy to enter the information, but if you need to repeat the exchange of information, build a program code to make an entry more efficient. You can use Bulk INSERT to store in a way. SQL file, create a command code file. Exchange Elf Use Exchange Elf to project data from the database to the external destination. Exchange wizards and BCPs can exchange data to destinations other than the information file. The following is the step of using the exit wizard:
In Enterprise Manager, an server group is expanded and the server you want to project will be used. From the tool menu, select the Elf, then expand the data conversion service folder, select the DTS to exchange the wizard, press the determination, the data conversion service will appear - into / out the wizard screen, as shown in Figure 24-13.
Press Next to enter the selection data source screen, as shown in Figure 24-14. Here you can specify the source, you can keep the preset value Microsoft OLE DB Provider for SQL Server setting, or select Microsoft ODBC Driver for SQL Server. These two options can be connected to SQL Server. Other options can be used to exchange information from other types of database. Choose a database to continue the procedure, in this case we select the Northwind repository. You can change the advanced options by advancement buttons, such as connecting, online location, network program libraries, and workstation IDs, etc., but these properties usually do not need to be changed.
Figure 24-13 "Data Conversion Service - Infant / Exchange Elf"
Figure 24-14 "Select Source" screen
Press Next to enter the selection destination screen, as shown in Figure 24-15. This picture will vary depending on the destination of the selected destination, but in most cases, you will ask you to enter login and archive information. In this case, we choose Text File as the destination, omit the requirements for entering login and archive information, and store the data sheet as a text file. Enter the destination file name in the archive name text block. Figure 24-15 "Select Destination" screen
Press Next to enter the specified data table to copy or query the screen, as shown in Figure 24-16. This picture can specify whether to exchange the entire information list, or use the query to exchange some of the information sheets. If you have selected other SQL Server libraries as output destination, you can use the options between the SQL Server Database Documents.
Figure 24-16 "Specified data table copy or query" screen
If you choose to use the query to specify the transferred information, press Next, type the SQL statement, as shown in Figure 24-17. Here you can type SQL statement, just select the information you want to remarkably. The query can be used to select a subset of the information line or the information column, or select the entire information list.
Figure 24-17 "Type SQL Statement" screen
Select Next to enter the selection file format screen, as shown in Figure 24-18. (If you enter this screen from typing SQL Server statement, the drop-down list will not be displayed in the source.) In this screen, you can specify several format options for the destination file, including whether the file uses a separate symbol or fixed field. . Press Next after selecting the format. If you choose the data sheet and view table of the replication source database, press the next step to enter the selected destination file format screen, in this way, enter this picture, the source will show a list. Press Next after selecting the format.
Figure 24-18 "Select Purpose Format" screen
Now enter the storage, scheduling, and replicate package, as shown in Figure 24-19. Here you can set whether the DTS package is stored in order to perform, set the method, and the previously introduced to the wizard, just the option here will execute the extension in a reverse manner.
Figure 24-19 "Storage, Scheduling and Copy Package" screen
Press the next step to enter the DTS return / propalency sprite screen, as shown in Figure 24-20.
Figure 24-20 "DTS Remission / Exchange Elf"
When you press the completion button, the discharge program starts to execute, and the encapsulation screen is being executed, as shown in Figure 24-21, there will be a message that has been successfully or failed after the program is completed. The use of the elf and the exit wizard simplifies many sets of cumbersome steps, but please remember if you need to repeat the emission or exchange action, spend some time write instruction code saves the time of repeated execution. You can create instruction codes that use BULK INSERT statements to perform exchanges or use SELECT statements.
Description In the example, we use the conversion between text files and databases. In fact, the wizard can perform more data-type conversions, especially for the data distribution or other data sources, especially useful.
Figure 24-21 "Package" screen
The Staging Data Sheet Stagening Data Sheet is a temporary data sheet that you can use to establish, process and operations will load SQL Server, and copy them to the appropriate data sheet inside the database. This section learns how to use the Staging Data Sheet. The Staging Data Sheet Basic Staging Data Sheet is a temporary archive storage area in the database, and you can copy your data into the Staging Data Sheet. You can then use T-SQL to perform an operation such as association, convert the data into a desired format, and more. Such transformations need to use the Staging data sheet and other existing information sheets. Use the Staging Data table to be loaded, eliminating certain information loading methods to load limitations. Most of the data load technology can only make the data to the database without handling. With DTS, some data conversion can be performed, but unable to convert existing information in the database. The main advantage of using the Staging Data Sheet is that you can perform joint operations based on information in the status of the Staging Data Sheet itself. Using the Staging Data Table This section will use the Staging Data Table in this section, which is the merger and load data sheets, load and split data sheets, and load unique values to a data sheet. These three examples can demonstrate staging materials. Table How to assist in loading. Merge and load data sheets Imagine a data sheet in the data supermarket (Data Mart), which consists of two data sheets in the online trading (OLTP) system, which are A, B, C, D, E five data lines. Data lines A, B, C are in the same information table, and the data lines C, D, E are in another information table. Both of these input data can be temporarily stored, and using a coupling operation to load the data supermarket, Fig. 24-22 is an operational illustration. Figure 24-22 Using the Staging Data Sheet to perform the coupling operation
In the first example, the load and division data sheet should be loaded into the digital table in the data supermarket in accordance with normal conditions (for normalization purposes). To make this job easily, please copy the information to the Staging Data table, and then use two queries to load the data in the Staging Data table into the data sheet in the data supermarket, as shown in Figure 24.
Figure 24-23 Split information using staging data sheet
Loading a unique value to a data sheet To ensure that the information is loaded, you can use a large number of copying methods to copy the data into the Staging Data table, and then use the T-SQL statement to put the value in the main data sheet, from The Staging Data Sheet is inserted into the main data sheet. This option will be useful when the loaded information may violate the business rules. Use the following statements to copy the unique value from the Staging Data table to the main data list:
INSERT INTO TABLE (Columna, Columnb)
Select Columna, Columnb
From staging_table
WHERE Columna Not in (SELECT Columna
From table
The statement looks complicated, in fact, it is only to confirm that there is no information in the main information table. If not, the two data lines (A and B) in the Staging Data table are loaded into the main data sheet. To ensure uniqueness of the insertion data value. SELECT ... INTO statement uses Select ... INTO statement is not a method of formal use to load the information, it should be said to be obtained from the existing information list or staging data sheet, then Establish a new information list, the method of loading the information.
Note Since SELECT ... INTO statement has certain restrictions, the Database Option Select INTO / BULKCOPY must be set to TRUE. To set this option, use the following T-SQL statement: exec sp_dboption
Select ... INTO statement grammar is as follows:
Select
INTO
SELECT_CLAUSE variables refer to statements or other options that can be used in combination with SELECT, such as from and where. SELECT ... INTO states are simple and easy to use, as follows.
Exec sp_dboption "example", "Select Into / Bulkcopy", TRUE
Go
Select ORDER_ID,
Contact_id,
Item_id,
Item_Description,
Amount Into Newsales
From stage
Go
Exec sp_dboption "example", "SELECT INTO / BULKCOPY", FALSE
Go
The name of the database here is "Example", establishing a data sheet name to newsales, and take the information table is Stage. This chapter summarizes this chapter, we have learned BCP, Bulk INSERT statements and DTS's way of use, and also introduce the Staging Data Sheet and SELECT ... INTO statements, which provide a lot of benefits because loading information is available. The library is one of the main tasks of DBA. Chapter 25 will begin to learn a decentralized trading coordinator (Microsoft Distributed Transaction Coordinator, MSDTC), and Microsoft Transaction Server (MTS).
