query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-09-01 : 21:25:55

How to loop through files in a specified folder, load one by one from source folder into a table using SQL stored procedure.

Please help.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 00:55:54

How the filenames are named? Is filenames followed some fixed format?
1) You can use BULK INSERT/ bcp command
2) You can create SSIS task for loading files data to tables

--
Chandu

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-09-02 : 08:28:58

Thanks

Yes...files are of same fixed format

We don't want use ssis instead we wanted to work with a stored procedure with input parameter a directory file location then it should able to load all the files data to a table.

Please provide me the t sql code to loop through the files..

Thanks for you help in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:41:50

quote:
Originally posted by sqlfresher2k7

Thanks

Yes...files are of same fixed format

We don't want use ssis instead we wanted to work with a stored procedure with input parameter a directory file location then it should able to load all the files data to a table.

Please provide me the t sql code to loop through the files..

Thanks for you help in advance..

if you want to use procedure you can use BULK INSERT

see

http://www.sswug.org/articles/viewarticle.aspx?id=17431

if you can use ssis see below
http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-09-03 : 08:55:29

Thanks Visakh

I m not sure if this works to my requirement...

My requirement is

I want a stored procedure which user will provide the directory name and the sp should process and load all the files data from the directory to the table..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-03 : 09:16:59

quote:
Originally posted by sqlfresher2k7

Thanks Visakh

I m not sure if this works to my requirement...

My requirement is

I want a stored procedure which user will provide the directory name and the sp should process and load all the files data from the directory to the table..

you just need to add the looping logic to get the files from directory

use xp_cmdshell to load file list onto a table and then use it for iteration within loop.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources