Click here to Skip to main content
15,913,304 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am creating a store procedure to store some values in data base table using Bulk Insert as following:

SQL
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


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 :)
Posted
Updated 6-May-12 19:49pm
Comments
Herman<T>.Instance 7-May-12 1:50am    
what is the value you set for @FileFieldterminator ?
Ambesha 7-May-12 3:36am    
Thanks for your reply. I have set "|" (Pipe) for Feild terminator and if I change the decimal data type to varchar in Database then it works fine but I need to store value in Decimal data type feild in SQL DB.
Sandeep Mewara 7-May-12 2:17am    
You get any error? What exactly is happening?
Ambesha 7-May-12 3:51am    
Thanks for your reply.I am getting following error:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 2 (Latitude).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Maciej Los 7-May-12 16:08pm    
Have you seen my answer? You need to create "format file" using bcp tool...

1 solution

Below text comes from MSDN[^]
String-to-Decimal Data Type Conversions


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....
 
Share this answer
 
Comments
Sandeep Mewara 9-May-12 3:02am    
5!
Maciej Los 9-May-12 3:19am    
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900