Importing Multiple csv files into one SQL Table

Question

Importing Multiple csv files into one SQL Table

Viewing 10 posts - 16 through 24 (of 24 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

MysteryJimbo (5/18/2012)

*cringe*
Sorry. I've seen xp_cmdshell and similar methods like that before. Oooo and a cursor. 🙂

awe come on; whether SSIS or anything else, multiple files requires a loop of some sort; in this case it's perfectly acceptable to loop thru items that are not in a datatable.

BULK INSERT is the the fastest method to do the work.

the knee-jerk reactions to cursors and xp_cmdshell have their place, but there are exceptions.

the added bonus here is there is a ZERO learning curve compared to SSIS....most posters can copy-paste-adapt, where it might take a while to get their first SSIS example up and running, let alone going thru the learning curve of errors from SSIS.

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!

david.tyler SSCrazy Points: 2206 More actions · Answer 2

I do agree faster copy/paste solutions work in a pinch, however spending time working your first SSIS solution will pay off down the road. First, you have new ammo for future issues and second, the reward of learning something new that holds resume value is priceless. 🙂

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

Lowell (5/18/2012)

MysteryJimbo (5/18/2012)
*cringe*
Sorry. I've seen xp_cmdshell and similar methods like that before. Oooo and a cursor. 🙂
the knee-jerk reactions to cursors and xp_cmdshell have their place, but there are exceptions.

I fully agree for the most part. Ive just experienced bad usage of both and xp_cmdshell isnt always an option in a secure environment

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 4

MysteryJimbo (5/18/2012)

Lowell (5/18/2012)
MysteryJimbo (5/18/2012)
*cringe*
Sorry. I've seen xp_cmdshell and similar methods like that before. Oooo and a cursor. 🙂
the knee-jerk reactions to cursors and xp_cmdshell have their place, but there are exceptions.
I fully agree for the most part. Ive just experienced bad usage of both and xp_cmdshell isnt always an option in a secure environment

Heh... with more irony than most people can propably stand, if you [font="Arial Black"]can't[/font] run xp_CmdShell safely, then your environment[font="Arial Black"] isn't [/font]actually secure.

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

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

Lowell (5/18/2012)
I'll go all devils advocate on the SSIS guys and present a solution i put together for other posts that is all TSQL.
Thsi requires xp_cmdshell to get the list of files.

Hot Damn! Lowell for President! 🙂

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Jeff Moden (5/18/2012)
Lowell (5/18/2012)
I'll go all devils advocate on the SSIS guys and present a solution i put together for other posts that is all TSQL.
Thsi requires xp_cmdshell to get the list of files.
Hot Damn! Lowell for President! 🙂

BAH! Go back to Cobol you Luddites! SSIS is KING, SSIS is GOD, SSIS is... a PITA usually, but worth it.

Honestly, I'd personally usually do this via SSIS. The ForEach loop is simplistic to setup against a local package variable and a quick expression feeds it into flat file connection you use in a data flow. It's quite simple and is one of the first steps to doing more advanced mechanics in SSIS.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 7

Evil Kraig F (5/18/2012)
Jeff Moden (5/18/2012)
Lowell (5/18/2012)
I'll go all devils advocate on the SSIS guys and present a solution i put together for other posts that is all TSQL.
Thsi requires xp_cmdshell to get the list of files.
Hot Damn! Lowell for President! 🙂
BAH! Go back to Cobol you Luddites! SSIS is KING, SSIS is GOD, SSIS is... a PITA usually, but worth it.
Honestly, I'd personally usually do this via SSIS. The ForEach loop is simplistic to setup against a local package variable and a quick expression feeds it into flat file connection you use in a data flow. It's quite simple and is one of the first steps to doing more advanced mechanics in SSIS.

Honestly, I see no reason to go anywhere near SSIS. 😉 In fact, you don't need to go anywhere near xp_CmdShell for this, either. SSIS was written for people that don't know how to do this stuff in T-SQL. 😀

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

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 8

Evil Kraig F (5/18/2012)
BAH! Go back to Cobol you Luddites!

P.S. Heh... that's gotta be your 6 talking because your mouth knows better. How do you like your porkchops? 😛

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Jeff Moden (5/19/2012)
Evil Kraig F (5/18/2012)
BAH! Go back to Cobol you Luddites!
P.S. Heh... that's gotta be your 6 talking because your mouth knows better. How do you like your porkchops? 😛

Well Done, by preference. And of course it's the 6. Cobol + Luddite = Contradiction, they were early adopters. 😉 I'd missed the response to this somehow and was dredging through older posts looking for something, sorry about the delay.

One of the reasons I prefer to do this via SSIS is it's more organized to me. Yes, 90% (99% if you include CLR) of what you can do with SSIS you can do via T-SQL. It's what you're more familiar with, mostly. Neither is more powerful than the other, but I do find you need to go through more hoops to do some of the tasks in T-SQL (on the fly dependent transformations), the same way you have to hoop jump in SSIS for others (Updating multiple rows in existance on a table in a single transaction).

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 10

Agreed. It has a lot to do with personal preference. Since I'm a Luddite :-), I'm going to use Powershell for most of this... via xp_CmdShell. :-P:-P:-P

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