Need to load 4 different csv files into 4 diffrent tables using t sql in one sql can't use ssis

Question

Need to load 4 different csv files into 4 diffrent tables using t sql in one sql can't use ssis

nikki123

Ten Centuries

Points: 1338
More actions
February 10, 2015 at 7:22 am

#301012

I have written bulk insert and doing this manully but need to automate it and file location changes evertime .
BULK INSERT dbo.alpha
FROM 'C:\posted 2015-01-16\HOSPITAL\HOSPFY2010\hosp_2010_ALPHA.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)
BULK INSERT dbo.RPT
FROM 'C:\posted 2015-01-16\HOSPITAL\HOSPFY2010\hosp_2010_RPT.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)
Please let me know how to proceed

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, what is the pattern that you can use to find the file/filepath if it's dynamic? is it int he same folder + different file name?

you can use xp_cmdshell to run the dir command, and use the results + dynamic SQL, would that work?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nikki123 Ten Centuries Points: 1338 More actions · Answer 2

The folder name and file name changes everytime

Folder name would be - posted 2015-01-16

Sub Folder - Hospital

sub sub folder- HOSPFY2010

Files -

hosp_2010_A.CSV

hosp_2010_R.CSV

hosp_2010_V.CSV

hosp_2010_D.CSV

All file go to different tables

like

dbo.a

dbo.r

dbo.v

dbo.D

everytime need to create new database and new tables

Lowell SSC Guru Points: 323518 More actions · Answer 3

so, what is the pattern then? any file that ends in [_A.csv] goes into the [A] table, for example?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

a quick example adapted form my snippets:

declare @filename varchar(255),

@path varchar(255),

@sql varchar(8000),

@cmd varchar(1000)

--get the list of files to process:

--#########################################

SET @path = 'C:\Hospital\'

SET @cmd = 'dir C:\Hospital\*_A.csv /b/s'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

--#########################################

--cursor loop to process all "_A.csv" files

declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%_A.csv%'

open c1

fetch next from c1 into @path,@filename

While @@fetch_status <> -1

begin

--bulk insert won't take a variable name, so make a sql and execute it instead:

set @sql = 'BULK INSERT dbo.[A] FROM ''' @filename + ''' '

+ ' WITH (

DATAFILETYPE = ''char'',

FIELDTERMINATOR = '','',

ROWTERMINATOR = ''\n'',

FIRSTROW = 2

) '

print @sql

exec (@sql)

fetch next from c1 into @path,@filename

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

While I'm a great lover of xp_CmdShell, there's no need for it just to get the file names. You can use xp_DirTree "pathname",1,1 to get a list of the file names, if that's all you need to get.

--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)

nikki123 Ten Centuries Points: 1338 More actions · Answer 6

nikki123

Ten Centuries

Points: 1338

February 10, 2015 at 11:18 am

#1776709

Thank you , This really helped me for loading