Stored Procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

dougancil2009
Starting Member

17 Posts

Posted - 2009-09-01 : 11:42:17

I've only written one stored procedure before and I'm not being tasked to write a second one. I'm having some problems with some variables and was needing some help. The SP that I'm writing is designed to import files from a local drive and do a bulk insert into a database then there will be another procedure that archive's the file into another directory. I haven't gotten to the second part yet, but this first part is giving me problems. Any help would be appreciated.

Thank you

CREATE procedure ImportFiles
@FilePath varchar(1000) = 'B:\Transfer\' ,
@ArchivePath varchar(1000) = 'B:\Transfer\Archive\' ,
@FileNameMask varchar(1000) = '*.txt' ,
@MergeProc varchar(128) = 'MergeIVRData'

AS

set nocount on

declare @ImportDate datetime
select @ImportDate = getdate()

declare @FileName varchar(1000) ,
@File varchar(1000)

declare @cmd varchar(2000)

create table ##Import (s varchar(8000))
create table #Dir (s char(8000))

GO
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd +' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd +' with (FIELDTERMINATOR=''|'''
select @cmd = @cmd +',ROWTERMINATOR = ''' + char(10) + ''')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where s = @FileName

exec @MergeProc

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 12:02:00

See if this helps

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

dougancil2009
Starting Member

17 Posts

Posted - 2009-09-01 : 12:52:05

vijayisonly, I'll try that and see if it does the trick. Thanks for the link.

dougancil2009
Starting Member

17 Posts

Posted - 2009-09-01 : 13:42:00

Vijay,

I have a question. With this line:

dougancil2009
Starting Member

17 Posts

Posted - 2009-09-01 : 13:43:43

Vijay,

Sorry

With this line:
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "

Do I need to point the statement to where the file is located? In my case its 'b:\Transfer'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 13:53:06

this is a sample statement

BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')

replace @PathFileName with your file path.

dougancil2009
Starting Member

17 Posts

Posted - 2009-09-01 : 14:10:52

Vijay,

I am getting the following error when I replace @PathFileName with my directory:

Procedure 'ImportFiles' expects parameter '@PathFileName', which was not supplied.

I suspect that has something to do with this on line 2 of the code:

@PathFileName varchar(100),

It's still looking for a variable that will be called @PathFileName ... or that's how it appears to me.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 14:15:03

Did you read the article?

In the example shown...a stored procedure is created that accepts 3 input parameters...

@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint

Once the SP is compiled and ready to be executed...you can execute it as below..by passing the 3 parameters required...

EXEC ps_StudentList_Import 'c:\TxtFile1.txt',1, 1

you need to alter the parameters passed based on your requirement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources