Bulk Insert more than one CSV file

Question

Bulk Insert more than one CSV file

SG87

SSC Eights!

Points: 827
More actions
April 8, 2010 at 4:38 am

#213615

Hi All
I have got a stored procedure that will bulk insert many CSV files into a table, this is the code I have:-
USE [Test_SG]
GO
/****** Object: StoredProcedure [dbo].[sproc_SamsTest] Script Date: 04/08/2010 11:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[sproc_SamsTest]
@PathFileName varchar(100)
as
Declare @sql varchar(2000)
set @sql = "Bulk Insert test_sg.dbo.samtest2 from '"+@PathFileName+"' With (FIELDTERMINATOR = ',')"
Exec(@SQL)
GO
I would then run the following code
Exec dbo.sproc_SamsTest 'D:\TestFolder\*.csv
This is not working, I haven't had much experience with TSQL so this is my first attempt, can anyone please help?
I am not sure if I will have to run this query for each file, and is there a way this can be automated?

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Your code points to drive "D". That drive must be on the server and it must not be a "mapped drive". If you want the server to read from a drive other than one of it's own, you must use a full UNC path AND the login that SQL Server uses must be able to "see" the file either using a share or by having the server login have some elevated privs.

On our ETL servers (which are NOT public facing) and depending on what they're designed to do, we'll either setup a "file share" or give the server login some elevated "domain" privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there... Check if the following code works for you

IMPORTANT : Follow the comments in the code block and customize it with appropriate values for your needs...

--Performance improver.

SET NOCOUNT ON

--Local variable declaration; dont edit anything here.

DECLARE @nSQL VARCHAR(8000)

DECLARE @DIR_COMMAND VARCHAR(1024)

DECLARE @path VARCHAR(1024)

DECLARE @Temp_Table VARCHAR(64)

-- Put the drive where your csv files are placed here

SET @path = 'E:\Pras'

-- DO NOT EDIT this command ;

--this will only absorb the filenames from the above path

SET @DIR_COMMAND = 'dir /b "' + @path + '"'

--YOUR_TABLE_NAME

SET @Temp_Table = 'ABC'

DECLARE @FileList table(OutputList varchar(500) NULL)

INSERT INTO @FileList

exec xp_cmdshell @DIR_COMMAND

IF (RIGHT(@Path,1) = '\')

SET @Path = LEFT(@Path,LEN(@Path) - 1)

SET @nSQL = ''

SELECT @nSQL = @nSQL +

N' BULK INSERT ' + CAST(@Temp_Table AS nvarchar(100))

+ N' FROM ''' + CAST(CASE WHEN CHARINDEX('\',OutputList) = 0

THEN @Path + '\' + OutputList

ELSE OutputList

END as nvarchar(255)) + N''''

+N' WITH (FIELDTERMINATOR = '','')' + CHAR(10)

FROM @FileList

WHERE OutputList IS NOT NULL

PRINT @nSQL

-- Comment the above PRINT statment and uncomment the following

-- EXEC statment to execute the task

--EXEC (@nSQL)

Please inform us here if this code worked for you. 🙂

Cheers,

C'est Pras!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Yes and as Jeff pointed out, make sure your path is a shared drive with the account that is going to execute the query have ALL the necessary permissions to read a folder/file.

Cheers!!

SG87 SSC Eights! Points: 827 More actions · Answer 4

Hi COldCoffee

That code works brilliantly.

Thanks for all your help!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

ColdCoffee

SSC-Dedicated

Points: 39972

April 8, 2010 at 6:13 am

#1148175

Glad it helped you mate!! 😉

Cheers!!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

As a side bar, try the following and imagine the possibilities because it doesn't require xp_CmdShell (a lot of DBA's won't turn it on)...

EXEC Master.dbo.xp_Dirtree 'E:\Pras',1,1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)