• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Need help with this Bulk Insert stored proc.

hallo all,
well I have following SP:
CREATE PROCEDURE [dbo]._blkIns_FlatFile
@FileName varchar
AS
DECLARE @FullPath varchar
SET @FullPath  = 'D:\inetpub\ftproot\' + @FileName
BULK INSERT m1.dbo.[User]
FROM @FullPath
WITH
(CODEPAGE = 'ACP' , FIELDTERMINATOR ='\t' , ROWTERMINATOR ='\n' )
GO
but allways reciving an erorr in line 7 (FROM @FullPath).
What I want is to upload file and give a name of file to stored proc
and then make an bulk insert. Question is what is wrong here, and is this possible
to create a dynamic name of the file to insert from.
Erorr messages is Bad syntax in near from @Fullpath in Line 7.
Hope someone has some idea.
Thanx a lot
Regards
Zeljko
0
Zelja
Asked:
Zelja
1 Solution
 
illCommented:
CREATE PROCEDURE [dbo]._blkIns_FlatFile
@FileName varchar
AS
DECLARE @FullPath varchar
SET @FullPath  = 'D:\inetpub\ftproot\' + @FileName
declare text nvarchar(4000)
sert @text='
BULK INSERT m1.dbo.[User]
FROM ' +@FullPath +'
WITH
(CODEPAGE = ''ACP'' , FIELDTERMINATOR =''\t'' , ROWTERMINATOR =''\n'' )'
exec ( @text)
GO
0
 
ashishjjCommented:
Hi,

   Try this


CREATE PROCEDURE [dbo]._blkIns_FlatFile
@FileName varchar
AS

DECLARE @FullPath varchar
DECLARE @tstrQuery nvarchar(4000)

SET @FullPath  = 'D:\inetpub\ftproot\' + @FileName

SELECT @tstrQuery = ' BULK INSERT m1.dbo.[User] ' +
               ' FROM ''' + @FullPath + '''' +
               ' WITH (CODEPAGE = ''ACP'' , FIELDTERMINATOR =''\t'' , ROWTERMINATOR =''\n'' )'


-- execute the query
EXEC sp_executesql  @tstrQuery


GO

Ashish
0
 
xenon_jeCommented:
So all you can do is to use dynamic queries, because bulk insert requests you to fill the filename, not to use a variable there. (so create a variable in which you put the entire query, then execute it with
exec('string query...')
The solutions described above should work
0
 
CleanupPingCommented:
Zelja:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
ZeljaAuthor Commented:
thx a lot people
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now