在SQL中使用批量插入获取十进制值 [英] Use Bulk Insert in SQL for decimal values

问题描述

我正在创建存储过程，以使用批量插入将某些值存储在数据库表中，如下所示:

I am creating a store procedure to store some values in data base table using Bulk Insert as following:

CREATE PROC spTestBulkInsertFortblTestBM (@FilePath VARCHAR(100), @FileFieldterminator VARCHAR(5))
AS
	BEGIN
		--The number of rows affected by a Transact-SQL statement will not return
		SET NOCOUNT ON;
			--Truncate the existing data from table 
			TRUNCATE TABLE dbo.tblTestBM
			--Declare the local variable
			DECLARE @bulkinsert NVARCHAR(2000) 
			 
			SET @bulkinsert =  
				   'BULK INSERT dbo.tblTestBM
				   FROM ''' +  @FilePath + ''' 
				   WITH 
				   (
				    FIRSTROW = 1,
				    FIELDTERMINATOR = '''+ @FileFieldterminator +''', 
				    ROWTERMINATOR = ''\n''
				   )' 		
			--Execute the BULK INSERT statement				   	 
			EXEC (@bulkinsert)
			--Print the number of rows affected
			PRINT CAST(@@ROWCOUNT AS VARCHAR(5))+ ' Rows Inserted'
			RETURN (@@ROWCOUNT)
		--The number of rows affected by a Transact-SQL statement will return thereafter
		SET NOCOUNT OFF;
	END

但是我无法在表中插入十进制值.

文本文件中的数据-
-1001 | 57.00376611111 | 9.87443416687 |艾伦·斯坦纳|奥尔堡
-1002 | 57.00376611111 | 9.87443416687 | Kety Praye | Redmond

我们是否还需要将格式文件作为参数传递?如果是，那么请帮我获取格式文件的内容:)

But I am Unable to insert Decimal value in table.

Data in text file-
-1001|57.00376611111|9.87443416687|Alan Steiner|Aalborg
-1002|57.00376611111|9.87443416687|Kety Praye|Redmond

Do we need to pass a format file also as a parameter? if yes than please help me out for content of Format file :)

推荐答案

下面的文字来自

Below text comes from MSDN[^]

字符串到小数的数据类型转换

在大容量插入中使用的字符串到十进制数据类型的转换遵循与Transact-SQL CONVERT函数相同的规则，该函数拒绝表示使用科学计数法表示数值的字符串.因此，BULK INSERT将此类字符串视为无效值并报告转换错误.

要变通解决此问题，请使用格式文件将科学计数法浮点数据批量导入十进制列.在格式文件中，将列明确描述为实数或浮点数据. ...

The string-to-decimal data type conversions used in BULK INSERT follow the same rules as the Transact-SQL CONVERT function, which rejects strings representing numeric values that use scientific notation. Therefore, BULK INSERT treats such strings as invalid values and reports conversion errors.

To work around this behavior, use a format file to bulk import scientific notation float data into a decimal column. In the format file, explicitly describe the column as real or float data....

这篇关于在SQL中使用批量插入获取十进制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！