Bulk Insert

Question

Bulk Insert

lrosales

SSCommitted

Points: 1821
More actions
September 10, 2008 at 9:31 am

#93814

Y'all
🙂
I need an extra set of eyes looking into this procedure I am attempting to create. can someone see what I am Missing. keep getting an error on the @WolePath variable.
Create Procedure usp_ImportTable
(
@FileNamenvarchar(50)
)
as
--Declare database, Owner and the path as well as the filename
declare @dbnvarchar(15)
declare @dbonvarchar(15)
declare @Pathnvarchar(50)
declare @wholepath nvarchar(80)
set @db = 'Northwind'
set @dbo = 'dbo'
set @wholepath = @db +'.'+ @dbo+'.'+ OrderDetails
--validate the
if @FileName <> isnull
set @path = @FileName
else
set @Path = 'D:\ImportTable.txt'
--Where is going to go into.
BULK INSERT @WholePath
FROM @Path
WITH ( FIELDTERMINATOR = ',',
ROWTERMINATOR = '' )
thanks y'all
😉
"We never plan to Fail, We just fail to plan":)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 1

lrosales (9/10/2008)
Y'all
🙂
I need an extra set of eyes looking into this procedure I am attempting to create. can someone see what I am Missing. keep getting an error on the @WolePath variable.
Create Procedure usp_ImportTable
(
@FileNamenvarchar(50)
)
as
--Declare database, Owner and the path as well as the filename
declare @dbnvarchar(15)
declare @dbonvarchar(15)
declare @Pathnvarchar(50)
declare @wholepath nvarchar(80)
set @db = 'Northwind'
set @dbo = 'dbo'
set @wholepath = @db +'.'+ @dbo+'.'+ OrderDetails
--validate the
if @FileName <> isnull
set @path = @FileName
else
set @Path = 'D:\ImportTable.txt'
--Where is going to go into.
BULK INSERT @WholePath
FROM @Path
WITH ( FIELDTERMINATOR = ',',
ROWTERMINATOR = '' )
thanks y'all
😉

OrderDetails is invalid - specifying it in that way means it's a column, but you're not defining where the column comes from. I'd guess it should be 'OrderDetails'

Also - ii you're trying to base your condition on whether the value of @FileName is null, the syntax would be @FileName IS NULL. IsNull is a T_SQL function which retuens the second vallue in the function if the first contains a null value

eg ISNULL(@FileName, 'Hello') retunrs 'Hello' if @fileName is null

lrosales SSCommitted Points: 1821 More actions · Answer 2

Thanks

all the suggestion you mention have been tried and still get a failure. the failure is refering to the actual Bulk Insert statement,

Bulk Insert @WholePath

from @path

with ......

thanks for the info.

🙂

"We never plan to Fail, We just fail to plan":)

gbd77rc Ten Centuries Points: 1031 More actions · Answer 3

Hi,

It looks like BULK INSERT does not support variable names. Try something like this instead.

declare @db nvarchar(15)

declare @dbo nvarchar(15)

declare @Path nvarchar(50)

declare @wholepath nvarchar(80)

declare @sql nvarchar(1000)

set @db = 'AdventureWorks'

set @dbo = 'dbo'

set @path = 's:\test.txt'

set @wholepath = @db +'.'+ @dbo+'.'+ 'ErrorLog'

select @wholepath, len(@wholepath)

set @sql = 'BULK INSERT ' + @wholepath + ' FROM ''' + @path + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = '''' )'

select @Sql

execute(@sql)

You may what to replace execute with sp_executesql for better security and performance.

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

lrosales SSCommitted Points: 1821 More actions · Answer 4

Thanks

I arrived to the same conclusion and did something similiar to the suggestion you made. and the Problem was solved.

thank y'all

😛

"We never plan to Fail, We just fail to plan":)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 5

Glad you got it sorted - and appologies for the abysmal typing btw