Member Avatar for 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

  • 2 Contributors
  • 2 Replies
  • 199 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dougancil

Dani AI

Generated

Quick summary for (and thanks to for asking for the sample): the input is a simple CSV with two fields (ID and phoneNumber), so the BULK INSERT command you build must match that shape and the file’s delimiters. Common mistakes to avoid here are wrong FIELDTERMINATOR, using only LF when the file is CR+LF, and truncating the dynamic SQL variable. Use a small staging table that matches the incoming columns (for example, ID INT, phoneNumber VARCHAR(20)) rather than a single huge varchar column unless you plan to parse lines afterwards.

A minimal, safe pattern to build and run the BULK INSERT dynamically (note the escaping and NVARCHAR usage) is:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = N'BULK INSERT dbo.ImportStaging FROM '''
    + REPLACE(@File,'''','''''') + N''' 
    WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\r\n'', FIRSTROW = 1, TABLOCK);';

EXEC sp_executesql @cmd;

Practical checklist and gotchas:

  • After the BULK INSERT, verify success with @@ROWCOUNT or a SELECT COUNT(*) on the staging table before calling the merge proc. If the merge proc name is stored in a variable, execute it with dynamic SQL (for example, SET @cmd = N'EXEC ' + QUOTENAME(@MergeProc) + N';' then EXEC sp_executesql @cmd;) — EXEC @MergeProc will not execute a proc whose name is in a string variable.
  • Wrap the import + merge + file-move in TRY/CATCH and only move the file to Archive on success.
  • xp_cmdshell is convenient but disabled by default and a security risk; consider using SQL Agent, SSIS, or a PowerShell script to list/move files. Ensure the SQL Server service account has proper read/write rights on the folders.
  • If your source can contain quoted fields or embedded commas, use a format file or import into staging and parse with a robust parser.

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.