This is 'a depends' answer.
You can use either, if all the csv files are of the same format then you could BULK INSERT to a staging table before transfering to output tables, this is what I do.
This is the code I use to transfer files, beware it uses a cursor.
CREATE TABLE #files
(
rowid int IDENTITY(1,1),
fname varchar(255) NULL
)
DECLARE@filename varchar(255),
@count int,
@sql nvarchar(500),
@result int,
@cmd nvarchar(4000)
INSERT#files
(fname)
EXECUTE@result = master.dbo.xp_cmdshell 'DIR \\servername\folder /a-d /b'
IF EXISTS (SELECT * FROM #files WHERE fname = 'File Not Found')
BEGIN
PRINT' No Files Found'
RETURN 0
END
IF @result <> 0
BEGIN
PRINT'Command (DIR) Failed. Result = '+CAST(@result as varchar)
RETURN 1
END
IF NOT EXISTS (SELECT fname FROM #files WHERE fname IS NOT NULL)
BEGIN
PRINT'Failed to Retrieve Files'
RETURN 1
END
DECLARE filecursor CURSOR FOR
SELECTfname
FROM#files
WHEREfname IS NOT NULL
OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
BULK INSERT .....
FETCH NEXT FROM filecursor INTO @filename
END
CLOSE filecursor
DEALLOCATE filecursor
Far away is close at hand in the images of elsewhere.
Anon.