I. Use of BCP Tools)
Q: Can I use the SQL statement in SQL Server to pour the text file into the database into a table?
A1:
Use the BCP utility BCP utility to copy data in the format specified by the Microsoft® SQL ServerTM 2000 instance and data files.
Syntax BCP {[[Database_name.] [Owner].] {Table_Name | View_name} | "query"} {in | out | queryout | format} Data_file [-m max_erroat} [-f format_file] [-e err_file] [- F first_row] [-l last_row] [-b Batch_Size] [-N] [-C] [-w] [-N] [-V (60 | 65 | 70)] [-6] [-q] [- C code_page] [-t field_term] [-r row_tem] [-o output_file] [-A packet_size] [-S server_name [/ instance_name]] [-u login_id] [-P password] [-t ] [-V] [-r] [-K] [-e] [-h "hint [, ... n]"]
Parameter Database_name
The name of the database where the specified table or view is located. If not specified, the user defaults the database.
Owner
The name of the table or view owner. If the user who performs a large-capacity replication operation has a specified table or view, Owner is optional. If you do not specify Owner and perform a large-capacity replication operation does not have a specified table or view, Microsoft® SQL ServerTM 2000 will return an error message and cancel a large-capacity replication operation.
Table_name
It is a destination table name that copies the data to the SQL Server (IN), and the source table name when copying data (OUT) from SQL Server.
View_name
It is the source view name that copies the data to the SQL Server (IN), and the source view name when copying data from SQL Server (OUT). Only the views of the same table can be used as the destination view. See INSERT for more information on copying data to the limits of the view.
Query
Is the Transact-SQL query that returns a result set. If the query returns multiple result sets, such as specifying the SELECT statement of the compute clause, only the first result set is copied to the data file, and the subsequent result set is ignored. Use dual quotes to cause query statements, using single quotes to cause anything embedded in the query statement. When copying data from the query, you must also specify Queryout.
IN | OUT | Queryout | Format
Specifies the direction of mass replication. IN is copy from the file to the database table or view, and OUT refers to the file from the database table or view. Queryout must only be specified when copying data from a large capacity in the query. Format will create a format file based on the specified option (-n, -c, -w, -6 or -n) and table or view separator. If you use Format, you must also specify the -f option.
Note The BCP utility in Microsoft SQL Server 6.5 does not support large-capacity copies to tables containing SQL_VARIANT or BIGINT data types.
Data_File large-capacity replica table or view to disk (or copy) the full path to the data file when copying. This data file contains data that will be copied to the specified table or view when copying the data large capacity to SQL Server. When data is replicated from SQL Server, the data file contains data replicated from the table or view. The path can have 1 to 255 characters. -m max_errors Specifies the maximum number of errors that may be generated before a large-capacity replication operation. Each row that BCP cannot be copied will be ignored and is an error. If this option is not included, it is default that 10. -f format_file Specifies the full path to the format file, which contains storage responses when using BCP before the same table or view. This option is used when using the format file created by the Format option to copy or copy data. The creation of format files is optional. After prompting a few format issues, the BCP will prompt to save an answer in the format file. The default file is named bcp.fmt. When you copy data, BCP can reference a format file, so you don't have to re-interact forward the previous answer. If this option is not used, the -n, -c, -w, -6 or -n is specified, and the BCP will prompt the input format information. -e err_file Specifies the full path to the error file, this error file is used to store BCP to transfer all rows from files to the database. Error information from BCP will be sent to the user workstation. If this option is not used, the error file is not created. -F first_row Specifies the number of orders of the first row to be copied. The default is 1, indicating the first line of the specified data file. -L last_row Specifies the number of orders of the last line to be copied. The default is 0, indicating the last line in the specified data file. -b batCh_size Specifies the number of rows in each batch of data copied. Each batch is copied as a transaction to the server. SQL Server submits or rolls back (when failure), each batch transaction. By default, all data in the specified data file are copied as a batch. Please do not use with the -h "rows_per_batch = bb" option. -n uses the native (database) data type of data to perform a large-capacity copy operation. This option is not prompted to enter each field, which will use this machine. -c Use the character data type to perform a large-capacity replication operation. This option does not prompt to enter each field; it uses char as a storage type, without prefix, / t (tab) as a field separator, / n (newline) as a row terminator. -w Use the Unicode character to perform a large-capacity replication operation. This option is not prompted to enter each field; it uses nchar as a storage type, without prefix, / t (tab) as a field separator, / n (newline) as a row terminator. Can't be used in SQL Server 6.5 or earlier. -N The native (database) data type of non-character data uses data, and the character data uses the Unicode character type to perform a large-capacity replication operation. This is a higher performance option that can replace -w options, which is to transfer data from one SQL Server to another using data files. It does not prompt to enter each field. This option can be used when data that needs to be transmitted containing an ANSI extended character and want to utilize the performance of this machine mode. The -n option cannot be used in SQL Server 6.5 or earlier. -V (60 | 65 | 70) Perform a large-capacity replication operation using the data type in the early versions of SQL Server. This option is used with characters (-C) or native (-n) format. This option does not prompt to enter each field, which uses the default value.
For example, if you want to copy the date format of the BCP utility in SQL Server 6.5 (but ODBC no longer support) to SQL Server 2000, the -v 65 parameters can be used. Important When you copy data from SQL Server to a data file, even if you specify -V, BCP utility does not generate SQL Server 6.0 or SQL Server 6.5's date format for any DateTime or SmallDateTime data. Dates will always be written in ODBC format. In addition, since SQL Server 6.5 or earlier does not support Bit data, the null value in the bit column is 0. -6 Use SQL Server 6.0 or SQL Server 6.5 data type to perform large-capacity replication operations. Only for keep backward compatibility. Change to use the -v option. -q executes the SET quoted_identifiers on statement in the connection of the BCP utility and SQL Server instance. Use this option to specify the name of the database, owner, table, or view containing spaces or quotes. The entire table name or view name consisting of three parts is in double quotes (""). -C code_page is only for backward compatibility. Instead, specify a sort rule name for each column in the format file or interactive BCP. Specify the data code page in the data file. CODE_PAGE is only useful when the data contains a character value greater than 127 or less than 32. Code page value Description ACP ANSI / Microsoft Windows® (ISO 1252). The default code page used by the OEM client. If -c is not specified, this is the default code page used by BCP. RAW does not take the conversion from one code page to another code page. This is the fastest option because there is no conversion.
-P password Specifies the password of the login ID. If this option is not used, the BCP will prompt the password. If you do not have a password, this option is used to command the end of the line, and the BCP will use the default password (NULL). -T Specifies BCP to connect to SQL Server through trust connection using the secure credentials of the network user. No login_id and password. -v report the version number and copyright of the BCP utility. -R Specifies to copy the currency, date and time data large capacity to SQL Server using the area format defined for the area settings of the client computer. The area settings will be ignored by default. -k Specifies that a null value should be retained in a large-capacity replication operation instead of imparting the default value for the inserted column. -E Specifies the value of the identity column appears in the file to be imported. If no -e is given, the identifier value of this column will be ignored, and SQL Server 2000 automatically assigns unique values based on the seed value and increment value specified during the creation table. If the value of the identity column is not included in the table or view of the data file, you should skip the identity columns in the table or view when you specify the import data; SQL Server 2000 will automatically assign the column assigned a unique value. See DBCC Checkident for more information. -h "hint [, ... n]" Specifies the prompt used when large capacity replication data to the table or view. This option cannot be used when you copy data to SQL Server 6.x or earlier. Tip Description ORDER (Column [ASC | DESC] [, ... N]) data file sort order. If the data to be loaded has been sorted according to the aggregation index in the table, it will increase the large capacity replication A2:
BCP can be imported exported to data. Exec master..xp_cmdshell 'bcp test.dbo.p_aspect in c: /temp1.txt -c -q -s "servername" -u "-p" "" "" "
EXEC MASTER..XP_CMDSHEC 'BCP TEST.DBO.P_ASPECT OUT C: /TEMP1.TXT -C -Q -S "ServerName" -u "-p" "" "" export
A3.EXEC MASTER..XP_CMDSHELL 'BCP PUBS.DBO.AUTHORS OUT C: /TEMP1.TXT -C -Q -S "172.25.10.66" -u "sa" -p "888"' a4, .declare @FileName Varchar (50), @BCPCommand varchar (2000)
Set @FileName = Replace ('C: / Authors _' Convert (Char (8), getDate (), 1) '. Txt', '/', '-')
Set @bcpcommand = 'bcp "Select * from pubs.dbo.authors Order by au_lname" queryout "' set @bcpcommand = @BCPCommand @filename '" -u sa -p 888 -c'
EXEC MASTER..XP_CMDSHELL @BCPCommand
Second. (Use of Buik Tool)
Use Pubsgoselect * INTO PUBLISHERS2 from Publishersgo
- To copy data from Newpubs.dat to Publishers2, you can use the following command: Exec master..xp_cmdshell 'bcp pubs.dbo.publishers2 out c: /newpubs.dat -c -t, -r / n - S "172.25.10.66" -u "sa" -p "888" - Alternatively, you can use the BULK INSERT statement to use the BULK INSERT statement in the query tool such as the SQL query analyzer.
Bulk INSERT PUBS.DBO.PUBLISHERS2 from 'c: /newpubs.dat'with (DataFiletype =' Char ', Fieldterminator =', ', Rowterminator =' / n ')
