How to import data from multiple CSV files into the same table

Question

Post reply

How to import data from multiple CSV files into the same table

SQLUserC

SSCrazy

Points: 2345
More actions
March 9, 2009 at 8:42 pm

#200587

Hello,
I have a question about importing data in SQL 2000. I have 3 CSV files that I want to import into the same table. They have the same number of fields, same format in each of the fields, etc. Assuming I can't merge the files manually and import them as one merged file, is there a way to import the CSV files into the same table (i.e. import one data file and import the remaining two files to the table that importing the first one created)? I would greatly appreciate any information that anyone could provide.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

sudarram SSChasing Mays Points: 623 More actions · Answer 1

You can use DTS to import the CSV files one by one.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

See this article on Looping, Importing and Archiving files in DTS Packages using ActiveX Script

--Ramesh

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 3

Ramesh (3/14/2009)
See this article on Looping, Importing and Archiving files in DTS Packages using ActiveX Script

Just do 3 simple bulk inserts. It's also one of the fastest ways to do it.

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Jeff Moden (3/14/2009)
Ramesh (3/14/2009)
See this article on Looping, Importing and Archiving files in DTS Packages using ActiveX Script
Just do 3 simple bulk inserts. It's also one of the fastest ways to do it.

Jeff, you were right, it can also be done using BULK INSERT statement and some other methods as well. Actually, today, I was answering questions more about SSIS/DTS. So, I was in an impression of it, suggested the OP to use DTS. Though, I still suggest him to use DTS instead of BULK INSERT/OPENROWSET methods, 'cause I'm thinking that there are not just 3 files. There could be more than that or may be in future it will.

In case of n files, if we use BULK INSERT you have to use shell commands to read file names from the directory and loop through each file, which requires permissions on procedure xp_cmdshell and BULK ADMINISTRATORS role and most DBAs are reluctant to give permissions on these objects. Or let the application loop through directory and call the BULK INSERT procedure for each file and archive the file that are imported.

--Ramesh