Dynamic SQL Server Bulk IMport Syntax For File with Column Names in the First Row.

I need to modify a SQL Server Bulk Import SPROC statement slightly and I'm not sure of the syntax.

This is the statement I have used in the past
	set @sql = 'Insert into Balance_Import
	select * from openrowset( bulk '''+@ImportFileNameAndLoc+''' ,FORMATFILE = '''+@FormatFileNameAndLoc+''') as a'

Open in new window

The variables '@ImportFileNameAndLoc' and '@FormatFileNameAndLoc' are passed to the SPROC as parameters

It worked for file being imported that did not have field names in the first record of the imported file.

The import file I am working with now has field names in the first line so I need to add the  Firstrow=2 into the above statement.

How do I revise the original statement to add the 'Firstrow=2'?

If it helps here is the hard coded version of the Bulk import
--Insert into Balance_Import
--select * FROM OPENROWSET( BULK 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\Balances\MJOLibrary Summaries_TabDelim.txt', firstrow = 2, 
--FORMATFILE = 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\SQLImportFormatFiles\Balance_TabDelimited_Import_MArkW01.txt') as a

Open in new window

Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Add another parameter : @firstrow char(1) = 1

Make it the last parameter and you dont need to use it unless it is > 1 (ie 2 or more)

Means it will default to "1" if not supplied and you dont need to include

-- the parameters 
declare @firstrow char(1) = '2'
declare @ImportFileNameAndLoc varchar (500) = 'c:\mrwtest\ee\MJOTestSummariesTabDelimited.txt'
declare @FormatFileNameAndLoc varchar(500) = 'c:\mrwtest\ee\balance_import_format.txt'
-- then inside the proc

declare @sql varchar(max)

set @sql = 'Insert into Balance_Import
	select * from openrowset( bulk '''+@ImportFileNameAndLoc+''''+ iif(@firstrow > 1, ' , FIRSTROW='+@firstrow,'')+' , FORMATFILE = '''+@FormatFileNameAndLoc+''') as a'

print @sql

Open in new window

mlcktmguyAuthor Commented:
Thanks You
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.