CODEWITHSUNDEEP
sqlsql-serverbulkinsert
Salman Hasni
Salman Hasni

Reputation: 194

BULK insert parameterize FIRSTROW

Is there any way to parameterize the FIRSTROW in BULK INSERT QUERY

this is my select statement SELECT TOP 1 mid from movie_titles ORDER BY mid DESC

which return the integer value and i want to provide its result in bulk insert statement

BULK
INSERT movie_titles
FROM 'A:\Netflix\movie_titles.txt'
WITH
(
FIRSTROW = 'here i want to provide the result of above select query',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Upvotes: 1

Views: 827

Answers (2)

Rahul
Rahul

Reputation: 77876

AFAIK, you can't directly pass parameter to FIRSTROW. rather you can execute BULK INSERT as dynamic SQL like below (Not tested though but that will get you started)

DECLARE @midvar INT;
SELECT TOP 1 @midvar = cast(mid as INT) from movie_titles ORDER BY mid DESC;
DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT movie_titles
FROM ''A:\Netflix\movie_titles.txt'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+'' +
'FIELDTERMINATOR ='',''
FIRSTROW = ' + CAST(@midvar AS VARCHAR(10)) + ')';
EXEC(@bulk_cmd);

Upvotes: 2

Endrju
Endrju

Reputation: 2436

It's not possible to provide @variable name for FIRSTROW. If you want to do so, you need to create your BULK INSERT statement dynamically via string concatenation and execute it with EXEC or sp_execute.

Upvotes: 3

Related Questions