How to import multiple CSV files using MS SQL store procedure?

I had come across this task few days ago. Hence, put it here for future reference. The requirement is simple. System need to scan a specific folder and import all files into database. Ideally, these are the steps how I do it:

1. Create a import format file using BCP for BULK INSERT usage
2. Use master..xp_cmdshell to get all the file names in to table variable
3. Loop through the table and import data one by one using OPENROWSET and BULK

Create Sample Payment Table

Run Create_Payment_Table.sql to create a “Payment” Table. The columns must match with columns in CSV file.

Creating an XML Format file

Run

EXEC master..xp_cmdshell ‘bcp yourdbname.dbo.Payment format nul -T -t, -c -x -f C:\Payment.xml’

to create an XML format file for BULK INSERT usage.

Read the rest of this entry (moved to www.bronios.com) »

My Techie Life

Welcome! Finally I decided put start up a blog about my techie life. This because i do get a lot of issue in my working life which make me crazy to solve it. So, I will try to post the usefull tips and anything which is interesting. For my own records and may be will help someone out there too….

Updated: 2008-08-17
– I had moved this blog to http://www.bronxis.com.