USE monitor;EXEC dbo.TencentSp_DownloadFromCOS ...;
Parameter | Type | Default Value | Required | Description |
@BucketName | VARCHAR(200) | NULL | Required (when not in CleanupOnly mode) | COS bucket name (including AppId), for example, mybucket-1258888888. |
@FileName | VARCHAR(500) | NULL | Required (when not in CleanupOnly mode) | COS object path, for example, data/test.csv. After downloading, only the file name part is retained. |
@Region | VARCHAR(100) | NULL | Required (when not in CleanupOnly mode) | COS region, for example, ap-guangzhou, ap-shanghai. |
@OverwriteFile | INT | 0 | No | Whether to overwrite an existing local file with the same name. A value of 0 indicates no overwriting (the file is skipped if it already exists). A value of 1 indicates forced overwriting and re-download. |
@ExpectedFileSizeMB | INT | NULL | No | Expected file size (MB). After it is set, it checks whether the remaining space on the D drive is sufficient. When it is NULL, it defaults to requiring at least 500 MB of free space. |
@CleanupOnly | BIT | 0 | No | Only perform cleanup mode. 1 indicates that only expired local files are cleaned up, and no download is performed. |
@CleanupDays | INT | 1 | No | Clean up local files older than the specified number of days. 0 indicates that all files are deleted. |
Field | Description |
DownloadStatus | Download status. Success: The operation succeeded. AlreadyExists: The file already exists and was not overwritten. |
LocalFilePath | Complete path to which the file is downloaded locally, for example, D:\\dow_file\\58001\\test.csv. |
USE monitor;EXEC dbo.TencentSp_DownloadFromCOS@BucketName = 'mybucket-1258888888',@FileName = 'data/sales_2024.csv',@Region = 'ap-guangzhou';
EXEC dbo.TencentSp_DownloadFromCOS@BucketName = 'mybucket-1258888888',@FileName = 'data/large_file.csv',@Region = 'ap-guangzhou',@OverwriteFile = 1,@ExpectedFileSizeMB = 2048; -- The expected file size is approximately 2 GB. Check the disk space.
-- Clean up local files older than 7 daysEXEC dbo.TencentSp_DownloadFromCOS @CleanupOnly = 1, @CleanupDays = 7;-- Clean up all local filesEXEC dbo.TencentSp_DownloadFromCOS @CleanupOnly = 1, @CleanupDays = 0;
[a-zA-Z0-9.-]; FileName can only contain [a-zA-Z0-9._/ -]; Region can only contain [a-zA-Z0-9-]....USE monitor;EXEC dbo.TencentSp_BulkInsertFromFile ...;
Parameter | Type | Description |
@FileName | VARCHAR(500) | Source file name (excluding path), relative to the D:\\dow_file\\<port>\\ directory. |
@TargetDB | SYSNAME | Target database name (system databases prohibited: master, model, msdb, tempdb, monitor). |
@TargetSchema | SYSNAME | Target Schema name, typically dbo. |
@TargetTable | SYSNAME | Target table name (the table must already exist). |
Parameter | Type | Default Value | Description |
@FieldTerminator | VARCHAR(10) | , | The field delimiter. Use , for CSV files and \\t for TSV files. |
@RowTerminator | VARCHAR(10) | \\n | The row terminator. Use \\n for Linux-format files and \\r\\n for Windows-format files. |
@FirstRow | INT | 1 | Specifies the row from which to start the import. Setting it to 2 allows skipping the CSV header row. |
@LastRow | INT | NULL | Specifies the row at which to end the import. NULL indicates importing all rows. |
@Format | VARCHAR(10) | NULL | The file format, such as CSV. |
@CodePage | VARCHAR(20) | NULL | The code page, such as 65001 (UTF-8) or ACP (system default). |
@DataFileType | VARCHAR(20) | NULL | The data file type: char, native, widechar, or widenative. |
@FieldQuote | VARCHAR(5) | NULL | The field quote character, such as " (used with FORMAT='CSV'). |
@MaxErrors | INT | 0 | The maximum number of errors allowed. If this number is exceeded, the BULK INSERT operation fails and rolls back. |
@BatchSize | INT | NULL | The number of rows to import per batch. NULL indicates importing all rows at once. |
@KeepNulls | BIT | 0 | A value of 1 indicates that empty columns retain NULL (without using the table's default value). |
@KeepIdentity | BIT | 0 | A value of 1 indicates that the IDENTITY column values from the file are retained. |
@TabLock | BIT | 0 | A value of 1 indicates that a table lock is applied during the import (to improve performance for large batch imports). |
@CheckConstraints | BIT | 0 | A value of 1 indicates that constraints are checked during the import. |
@FireTriggers | BIT | 0 | A value of 1 indicates that INSERT triggers are fired during the import. |
@FormatFile | VARCHAR(500) | NULL | Format file name (relative to D:\\dow_file\\<port>\\) . When the format file name is specified, FieldTerminator and RowTerminator are ignored. |
@OrderHint | VARCHAR(500) | NULL | ORDER hint, such as col1 ASC, col2 DESC. Matching it with the clustered index can improve performance. |
@ErrorFile | VARCHAR(500) | NULL | Error row output file name (relative to D:\\dow_file\\<port>\\). |
Field | Description |
RowsImported | Number of rows successfully imported. |
USE monitor;EXEC dbo.TencentSp_BulkInsertFromFile@FileName = 'sales_2024.csv',@TargetDB = 'mydb',@TargetSchema = 'dbo',@TargetTable = 'SalesData',@FieldTerminator = ',',
EXEC dbo.TencentSp_BulkInsertFromFile@FileName = 'big_data.csv',@TargetDB = 'mydb',@TargetSchema = 'dbo',@TargetTable = 'BigTable',@FieldTerminator = ',',@FirstRow = 2,@TabLock = 1, -- Table-level lock improves performance@BatchSize = 50000, -- 50,000 rows per batch@MaxErrors = 10; -- Tolerates up to 10 error rows
EXEC dbo.TencentSp_BulkInsertFromFile@FileName = 'export.tsv',@TargetDB = 'mydb',@TargetSchema = 'dbo',@TargetTable = 'ImportTable',@FieldTerminator = '\\t',@RowTerminator = '\\r\\n',@FirstRow = 1,@KeepNulls = 1,@KeepIdentity = 1;
-- First, upload the formatted file to COS and download it.EXEC dbo.TencentSp_DownloadFromCOS@BucketName = 'mybucket-1258888888',@FileName = 'fmt/myformat.fmt',@Region = 'ap-guangzhou';-- Import using the formatted file.EXEC dbo.TencentSp_BulkInsertFromFile@FileName = 'data.dat',@TargetDB = 'mydb',@TargetSchema = 'dbo',@TargetTable = 'MyTable',@FormatFile = 'myformat.fmt';
[a-zA-Z0-9._/ -]; TargetDB / TargetSchema / TargetTable can only contain [a-zA-Z0-9_]....USE monitor;-- Step 1: Download the CSV file from COS.EXEC dbo.TencentSp_DownloadFromCOS@BucketName = 'mybucket-1258888888',@FileName = 'data/sales_2024.csv',@Region = 'ap-guangzhou';-- Step 2: Import the file into the user database table.EXEC dbo.TencentSp_BulkInsertFromFile@FileName = 'sales_2024.csv', -- Note: Only the file name is required, without the path.@TargetDB = 'mydb',@TargetSchema = 'dbo',@TargetTable = 'SalesData',@FieldTerminator = ',',@FirstRow = 2;-- Step 3 (Optional): Clean up local files.EXEC dbo.TencentSp_DownloadFromCOS @CleanupOnly = 1, @CleanupDays = 0;
フィードバック