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
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....
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)