A Better Solution

Question

Post reply

A Better Solution

dB-226266

Ten Centuries

Points: 1298
More actions
April 24, 2005 at 7:25 pm

#68978

I have the following process done within a Stored Procedure. I'm thinking of using bulk insert, xp_cmdshell and a cursor to deal with this... is there a better way?

Read a file, each line in the read file
Will be outputted to another file..

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

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

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 1

If you aren't making any modifications, seems like the old DOS COPY command would work better and faster.

What is it that you are really trying to do?

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

dB-226266 Ten Centuries Points: 1298 More actions · Answer 2

Well I'm trying to convert an Oracle PLSQL into SQLServer TSQL, and the logic for this particular Stored Procedure is the one i mentioned in my first post.. ..

On writing to the other file, other information is appended to it...

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

If the file you are trying to bring in is a text file (fixed field, CSV, Double-Quoted CSV, etc), you can use BCP or, perhaps, Bulk Insert which both operate in a similar fashion. I'd bring that information into a staging table that also has the columns I want to append to the output.

Use set based logic to update the appended columns in the staging table. Unlike Oracle, which seems to be tuned more favorably for cursors, MS-SQL Server cursors end up being quite a bet slower and more resource intensive than it's set based bretherin.

You can then use BCP or a query from OSQL to output to the other file.

Both BCP and OSQL can be executed using xp_CmdShell. Some DBA's disable xp_CmdShell because it's been identified as a security risk. In that case, you'd need to execute everything from a Cmd window which will still work just fine.

Depending on the type of input and output files you have, you may need to create a BCP format file for the data.

You could also use DTS to do all of this but I've had such good luck with the speed of BCP that I've never bothered with the DTS solution. I'm sure someone else will respond with how to do such a thing with DTS.

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

dB-226266 Ten Centuries Points: 1298 More actions · Answer 4

Great Idea you got there!.. I'll try that one. At least no more looping for me. thanks.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 5

in dts you would just create a source and destination text file. then create a transform task between them. then you can perform whatever actions you need on a column by column basis. not sure whether this would be faster than just writing a batch file using using a for /f loop and modifying each line. never compared the two on speed although i've used both.