Use the BCP and bulk into commands (to be updated)

Use the BCP and bulk into commands (to be updated)

Use the BCP and bulk into commands (to be updated)

 

The BCP command is a practical data export and import tool in sql2005. We can use this command to conveniently import data everywhere.

 

First, let's take a look at the BCP command format:

 

BCP is a command line tool used in SQL Server to import and export data. It is based on DB-library and can efficiently import and export large batches of data in parallel. In addition to running BCP on the console, you can also run BCP using SQL statements by calling a system stored procedure xp_cmdshell of SQL Server. For example:EXEC master..xp_cmdshell ‘BCP NTS.dbo.T_User out c:\User.txt -c -U"sa" -P"password"‘ 1. Four actions 01. Import: this action is used.INCommand is complete, followed by the file name to be imported 02. Export: this action is usedOUTThe command is complete, followed by the file name to be exported. The data source is a table or view. 03. Export using an SQL statement: Use the queryout command to complete this operation.OUTSimilarly, the data source is an SQL statement. 04. Export a format file: this operation is completed using the format command, followed by the Format File Name 2. Common options 01. -f Format_File Format_file indicates the format file name. This option depends on the preceding action.INOrOUT, Format_file indicates an existing format file. If format is used, it indicates the format file to be generated. 02. -x This option must be used with-F format_file to generate an XML format file. 03. -F First_Row Specifies the row from which the table is exported or the row from which the file is imported. 04. -L last_Row Specifies the end of the exported table or the end of the data imported from the imported file. 05. -c UseCharThe storage type, with no prefix and"\t"As a field delimiter"\n"As a line delimiter 06. -w Similar to-C, it is used only when the Unicode character set is used to copy data.nCharAs a storage type 07. -t Field_Term Specifies the delimiter. The default Delimiter is"\t" 08. -r Row_Term Specifies the line delimiter. The default value is"\n" 09. -S Server_Name[ \Instance_Name] Specifies the instance of the SQL Server server to be connected. If this option is not specified, BCP connects to the default instance of the local SQL Server. To connect to the default instance on a machine, you only need to specify the machine name. 10. -U Login_ID User name used to connect to SQL Server 11. -P Password User name and password used to connect to SQL Server 12. -T Specify bcp to log on to SQL Server using a trusted connection. -U and-P must be specified if-T is not specified 13. -k Specify empty column usageNULLInsert value, not the default value of this column 3. Export data 01. Export the entire table or view BCP NTS.dbo.T_User out c:\User.txt -c -U"sa" -P"password" -- Connect using a password (double quotation marks are required for the user name and password) BCP NTS.dbo.T_User out c:\User.txt -c -T -- Use trusted connection 02. Export the target data of the SQL statement BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:\User.txt -c -U"sa" -P"password" BCP "SELECT TOP 20 * FROM NTS.dbo.T_User" queryout c:\User.txt -F 10 -L 13 -c -U"sa" -P"password" -- Export 10th to 13 records 03. Export a format file BCP NTS.dbo.T_User format nul -f c:\User_format1.fmt -c -T -- Export table structure information to user_format1.fmt BCP NTS.dbo.T_User format nul -f c:\User_format2.fmt -x -c -T -- Export table structure information to XML 04. Import Data When importing data, you can import qualified records to the database based on existing format files. For example, the character length of the Third Field in the preceding format file is 24. If the length of the corresponding field in a text file exceeds 24, this record will not be imported into the database, other matching records are imported normally. BCP NTS.dbo.T_User in c:\User1.txt -c -T BCP NTS.dbo.T_User in c:\User1.txt -c -F 10 -L 13 -T -- You can use the-F and-l options to select the target data during import. BCP NTS.dbo.T_User in c:\User1.txt -F 10 -L 13 -c -f c:\User_format1.fmt -T -- Use a common format file BCP NTS.dbo.T_User in c:\User1.txt -F 10 -L 13 -c -x -f c:\User_format2.fmt -T -- Use an XML Format File 05. Example of actual export EXEC master..xp_cmdshell ‘BCP SZC.dbo.T_User out c:\test1.csv -c -U"sa" -P"qwert1"‘ -- Connect with a password EXEC master..xp_cmdshell ‘BCP SZC.dbo.T_User out c:\test1.csv -c -T‘ -- Use trusted connection 4. other methods to import and export data 01. Import Data SELECT * INTO authors1FROM OPENDATASOURCE(‘SQLOLEDB‘, ‘Data Source=192.1.1.2; UserID=sa; Password=qwert1‘).pubs.dbo.authors 02. Export data INSERT INTO OPENDATASOURCE(‘SQLOLEDB‘, ‘Data Source=192.1.1.2; UserID=sa; Password=qwert1‘).test.dbo.authorsSELECT * FROM pubs.dbo.authors INSERT INTO OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘,‘Text;DATABASE=c:\‘)[data#txt] SELECT * FROM text1 INSERT INTO OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0‘,‘Text;DATABASE=c:\‘, [data#txt]) SELECT * FROM text1 03. Import Excel Data SELECT * INTO excel FROM OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘, ‘Excel 5.0;DATABASE=c:\book1.xls‘ )[Sheet1$] 04. Import to excel INSERT INTO OPENDATASOURCE(‘MICROSOFT.JET.OLEDB.4.0‘, ‘Excel 5.0;DATABASE=c:\book1.xls‘ )[Sheet1$] SELECT * FROM excel

If you don't understand it, it doesn't matter. I have summarized how to simply use this command.

When SQL 2005 uses this command, it needs to open the Stored Procedure xp_cmdshell. By default, the stored procedure sql2005 is disabled. We use SQL statements to open it:

EXEC SP_CONFIGURE ‘show advanced options‘,1;reconfigureEXEC SP_CONFIGURE ‘xp_cmdshell‘,1;reconfigure