Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bulk Insert with FirstRow as a parameter

Author  Topic 

coagulance
Yak Posting Veteran

78 Posts

Posted - 2013-06-12 : 09:20:29
DECLARE @sPath varchar(100)
Declare @sRowDelimiter varchar(10)
Declare @sFieldDelimiter varchar(10)
Declare @iFirstRow int
Declare @sPath varchar(20)
Declare @sRowDelimiter varchar(5)
Declare @sFieldDelimiter varchar(5)

SET @sPath = 'C\Inline\Result1.csv'
SET @sRowDelimiter = '\n'
SET @sFieldDelimiter = ','
SET @iFirstRow = 2

BULK INSERT GE_TDM.Result1
FROM @sPath
WITH
(
FIRSTROW = @iFirstRow
FIELDTERMINATOR =@sFieldDelimiter,
ROWTERMINATOR = @sRowDelimiter
FIRE_TRIGGERS
);

Immediate help is appreciated


bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 09:49:23
BULK INSERT GE_TDM.Result1
FROM @sPath -- You can't use variable in the FROM clause

For this kind of queries you have to use dynamic queries

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 09:56:51
follow these two links to create Dynamic query....
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185110
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184344 -- Last Reply Dynamic Query posted by Bandi (Posted - 04/08/2013 : 05:08:27)


--Solution is as follows
DECLARE @sPath varchar(100)
Declare @sRowDelimiter varchar(10)
Declare @sFieldDelimiter varchar(10)
Declare @iFirstRow int
DECLARE @SQL VARCHAR(MAX) = ''

SET @sPath = 'C\Inline\Result1.csv'
SET @sRowDelimiter = '\n'
SET @sFieldDelimiter = ','
SET @iFirstRow = 2

SET @SQL = '
BULK INSERT GE_TDM.Result1
FROM ''' + @sPath + '''
WITH
(
FIRSTROW = ' + CAST( @iFirstRow AS VARCHAR(3)) +'
FIELDTERMINATOR = ''' + @sFieldDelimiter + ''' ,
ROWTERMINATOR = ''' + @sRowDelimiter + ''' FIRE_TRIGGERS );'

--PRINT @SQL
EXEC(@SQL);
--
Chandu
Go to Top of Page
   

- Advertisement -