Stored Procedure

[dougancil]

I've only written one stored procedure in the past and I'm being tasked again to write another one. The purpose of this SP is to do a bulk insert from a .txt file and then to move the file to an "archive" when it's done with the bulk insert. The archive part will be another SP but I'm having issues with the bulk insert part. Here's the code:

create procedure ImportFiles
@FilePath 		varchar(1000) = 'c:\Transfer\' ,
@ArchivePath		varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask		varchar(1000) = 'bcp*.txt' ,
@MergeProc		varchar(128) = 'MergeBCPData'

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 varchar(8000))
	
	/*****************************************************************/
	-- Import file
	/*****************************************************************/
	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
		[B]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) + ''')'[/B]
		
		truncate table ##Import
		
		-- import the data
		exec (@cmd)
				
		-- remove filename just imported
		delete	#Dir where s = @FileName
		
		exec @MergeProc

The problem I'm having is with the select commands, I have no idea what to put in here. Can someone please help?

Thanks

[sknake]

Why don't you post a sample of the data you're working with and what the goal is and we'll go from there. I don't know what to put in the select command without know what the data looks like and what the objective is.

[dougancil]

Scott,

Here is a sample of the data I'm using to test with:

There are four fields in the table that I want to create:

ID

phoneNumber

Result

Date

Here is the data:

1,5122061293

2,5122061293

3,5124583125

4,5124839014

5,5129049285

6,5129049285

7,5129049285

8,5129049285

9,5129049285

10,5129049285

The result and date fields are filled out by our server when another script is run.

Thank you

Doug