I have never tried this before.

Question

I have never tried this before.

simflex-897410

Hall of Fame

Points: 3881
More actions
October 7, 2010 at 8:26 am

#241382

Greetings again helpers.
I have a .csv file with 5 columns:
postID varchar(3)
datePosted varchar(7)
BookNumber int
Title varchar(100)
Amount Money
I want to import this .csv file into sql server.
I know how to do this by using BULK Insert
BULK INSERT Catalogues
FROM 'c:\filename.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
The problem that I have that this import is done regularly and data on csv file is usually different.
The way we want to go about is to check the db to see if the data we are about to import already exists.
If yes, update it. If no, insert it.
I don't believe BULK insert can do this.
Any help is greatly appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

You could use Bulk Insert to upload the data into a temporary table then compare that data with what's in the permanent table.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I personally would lean towards SSIS since I think it allows you more choices about what to do, and better control over security, but bulk insert will work fine.

I like Alvin's advice, though I would make a permanent staging table. Import the data there, then look at MERGE as a way to send this to your permanent table.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

I like Steve's advice. I prefer using SSIS but I know this not everybody feels the same way. That's why I did not recommend it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

Your right Alvin, I would have liked SSIS but have not used it before.

Do you guys have any easy to follow example?

Thanks alot guys.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Without using SSIS read this for an explanation and sample code.

http://technet.microsoft.com/en-us/library/bb522522.aspx

If you want to use the MERGE statement in an SSIS package refer to:

http://technet.microsoft.com/en-us/library/cc280522.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

simflex-897410

Hall of Fame

Points: 3881

October 7, 2010 at 1:24 pm

#1233901

Thanks a lot guys.

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

I may be in a minority here. To me, SSIS is a four letter word. 😉

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Jeff Moden (10/7/2010)
I may be in a minority here. To me, SSIS is a four letter word. 😉

There's nothing wrong with SSIS, but there are wrong ways to use SSIS. 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Alvin Ramard (10/7/2010)
Jeff Moden (10/7/2010)
I may be in a minority here. To me, SSIS is a four letter word. 😉
There's nothing wrong with SSIS, but there are wrong ways to use SSIS. 🙂

I've admitted not done performance tests on my own but my understanding is that even the Bulk Insert task is slower than just using Bulk Insert. And what's the big deal? SSIS doesn't really give you anything better for importing CSV's... it just allows you to do some of the coding by clicking and dragging.

Last but not least, I avoid using a permanent staging table like the plague because I might want to have more than one instance of the code running on different files for purposes of parallel loading. Most of the ETL systems I've designed have had to handle hundreds of files per day with multiple instances of the "importer" running. A permanent staging table would be a whole lot more difficult to manage for concurrance especially when it comes to the wholesale table locking during validation updates to the data in the table.

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Jeff Moden (10/10/2010)
Alvin Ramard (10/7/2010)
Jeff Moden (10/7/2010)
I may be in a minority here. To me, SSIS is a four letter word. 😉
There's nothing wrong with SSIS, but there are wrong ways to use SSIS. 🙂
I've admitted not done performance tests on my own but my understanding is that even the Bulk Insert task is slower than just using Bulk Insert. And what's the big deal? SSIS doesn't really give you anything better for importing CSV's... it just allows you to do some of the coding by clicking and dragging.
Last but not least, I avoid using a permanent staging table like the plague because I might want to have more than one instance of the code running on different files for purposes of parallel loading. Most of the ETL systems I've designed have had to handle hundreds of files per day with multiple instances of the "importer" running. A permanent staging table would be a whole lot more difficult to manage for concurrance especially when it comes to the wholesale table locking during validation updates to the data in the table.

I like SSIS but I will agree with you that it's not necessarily the best option for a simple file upload.

Using a permanent staging table, with multiple process running at the same time, and each one starting with "TRUNCATE TABLE ...", could lead to interesting, and unexpected (by some people), results.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]