I am trying to parameterize the LASTROW argument of a BULK INSERT query. The last row of data in the textfile I am importing in to SQL Server contains information about the number of rows in the textfile. And I plan to use that number to populate the value of the LASTROW argument of the BULK INSERT query.
An example of the textfile:
20161003|3504|1360|
20161003|3540|1441|
EOF|2
The query I am working on:
DECLARE @FilePath VARCHAR(500)
SELECT @FilePath = 'C:\path\textfile.txt'
DECLARE @file VARCHAR(MAX)
SELECT @file = (SELECT * FROM OPENROWSET(BULK N'C:\path\textfile.txt', SINGLE_CLOB) AS Content)
DECLARE @LastRow INT
DECLARE @LastRow_String VARCHAR(10)
SELECT @LastRow_String = (SELECT SUBSTRING(@file, CHARINDEX('EOF|', @file)+4, LEN(@file)))
SELECT @LastRow = (SELECT CAST(@LastRow_String AS INT))
DECLARE @Query VARCHAR(MAX)
SELECT @Query = ('BULK INSERT [Database].[dbo].[Table] FROM ''' + @FilePath +
''' WITH ( FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''|\n'', LASTROW = ' + @LastRow + ')')
When I execute the above query I get this error message:
Msg 245, Level 16, State 1, Line 11
Conversion failed when converting the varchar value 'BULK INSERT [Database].[dbo].[Table] FROM 'C:\path\textfile.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n', LASTROW = ' to data type int.
Any help is appreciated!
The problem is that LastRow is an integer; you may need to use LastRow_String for the concatenation into Query; if you are concerned about the white spaces, you can do ltrim(rtrim(@LastRow_String))