Bulk Insert with FirstRow as a parameter

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources