Import CSV File Data

Question

Post reply

Import CSV File Data

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

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

DesiGuju Old Hand Points: 303 More actions · Answer 1

David,

Here is my storedProcedure.

CREATE PROCEDURE CSVDump

AS

--Bulk Insert Into staging table

Bulk Insert Staging_CSVDump

From "C:\CSV.txt"

With

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\N'

&nbsp

--Update main table

UPDATE tblMain

SET Prize = Staging_CSVDump.Prize,

Quantity = Staging_CSVDump.Quantity,

FROM tblMain INNER JOIN

Staging_CSVDump ON tblMain.OrderNo = Staging_CSVDump.OrderNo

GO

Now, how can i schedule this to run twice a day ? I do not have job schedule option.

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 2

Sohil, I'm not sure if I could easily walk you through job creation without posting a bunch of screenshots, etc., so I'll show you where to look. In SQL Server Management Studio, Click on Help, then Index, and in type "jobs" in the "Look for:" textbox. Click on the listing that says "jobs [SQL Server Agent]" and have some fun.

By the way, your stored proc doesn't yet truncate the staging table, so you'd currently be appending the text file to it every time you run it. From your earlier posts, I'm guessing you want it truncated at the start of this stored proc.

DesiGuju Old Hand Points: 303 More actions · Answer 3

David,

I did added the "Delete From Staging_CSVDump" line at the end of procedure.

I will look into online help for job creation.

Thanks

DesiGuju Old Hand Points: 303 More actions · Answer 4

David,

Part of my stored procedure inserts data from staging table to main table. When entering data into main table is it possible to add "$" symbol for one of the field and then enter data ?

Thanks

DesiGuju Old Hand Points: 303 More actions · Answer 5

DesiGuju

Old Hand

Points: 303

June 14, 2007 at 9:51 am

#712995

Thanks,

I figure it out

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Please tell me you didn't add a dollar sign to some numeric data and store it as a varchar...

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

DesiGuju Old Hand Points: 303 More actions · Answer 7

Jeff,

Field is define as Varchar Datatype

Thanks

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

Are you storing numbers that you will calculate with in that column??? World of hurt getting ready to happen there... $ won't let you do calcs unless you first convert to MONEY and you probably have nothing in place to keep other non-numeric characters out of the column. Also, code will always be slower when doing math because of the implict conversions that will be done.

Like I said, "Please tell me you didn't add a dollar sign to some numeric data and store it as a varchar..." That's just so wrong on so many fronts... and I haven't even mentioned them all...

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

DesiGuju Old Hand Points: 303 More actions · Answer 9

Jeff,

I'm only storing $ symbol in field (varchar) for reporting. There will not be any calculation.

Thanks

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

Ok... whatever... that's still not the way to do it... the $ sign should be added in a SELECT (if you've gotta do it that way)... in truth, the formatting should be done by the GUI, if you have one. Really bad practice to store formatted info in SQL for more reasons than I could list in a simple post.

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