CURSOR FOR (Remove Comma, Create New Column)
-
Jeff,
The requirements for the report also extend into many more *frustrating* things but, long story short, I had to create a view to manipulate the data the way they wanted. This is the last piece. I ran a count on the Reason field (the one with the comma's) and there was a max of 5, but they only want 4. These fields need to be added to my view (35 + fields). The view is also scarey and has many Sub Selects in order to manipulate this *stuff*
So I need to join my temp table on this view. I figured I would add this to a DTS package and Drop the temp table when done processing - - any ideas here would be great! In addition, NOT ALL ID's have a response. - - so the temp table will not contain all of the ID's that live in the view.Does this help?
LuckyInc
-
I got it to work - - Move the Insert out of the loop and add
SET @Response1 = null
SET @Response2 = null
SET @Response3 = null
SET @Response4 = null
-
Now, I have the results, but will the DTS accept Temp tables in the results when it trys to transform?
Select * from myview mv
left join (Select Reason1,Reason2,Reason3,Reason4 from #ResponseList) as r
ON r.OpportunityId = mv.MyOpportunityId
-
LuckyInc,
you are really lucky!
If it's all about flat file report you don't neet to do anything!
Just replace commas in string with delimiters you gonna use for those files and they would never realise it was one string, not columns.
And get rid of bad habit using DTS.
Read about bcp in BOL and use its advantages.
_____________
Code for TallyGenerator -
Nice trick... but I assume that this column must be put at the end of the line to work?!?!
-
Yes, I had to use the (Select * ) and all values had to be at the end of my join.
For it to work in DTS, I had to Create a Global ##Table instead of a Local #Table. In addition, the table manipluation had to be placed in a stored proc and the select statement outside of the proc for DTS Service to translate the values.
exec MyProc
Select * from myview mv
left join (Select * from ##ResponseList) as r
ON r.OpportunityId = mv.MyOpportunityId
-
Again, for those who don't understand.
GET RID OF DTS!
You don't need DTS to create flat file.
Run you select from bcp command. Direct output to desired file.
Include your ResponseList and a last column in the query.
If field delimiter in your file is comma yu don't need to do anything. If not replace commas in ResponseList with that delimiter.
That's all!
_____________
Code for TallyGenerator -
Cool - - Just explaining how I did it.
Thanks for the advice.
-
-
So Sergiy, how would one want to do this with DTS ???
. -
Don't ask me.
I've got one DTS fan sitting 5 meters from me. He inbuilds in those DTS's unbelievable amount of fuctionality, 40 steps with 6 flow directions - is just OK for him.
I could explain why but I'm afraid I gonna be banned from this forum for the wirds I gonna use for this explanation.
One reason I can tell - VB script.
Ad I better stop here.
_____________
Code for TallyGenerator -
When the hell did DTS come into the picture? And why are you using that pig, Lucky?
Serqiy, I'm sorry you're sitting within shooting range of an ID-Ten-T
--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) -
Greenhorn here wondering what is so bad about DTS????
-
I suppose I spoke a bit out of turn by calling DTS a pig... it's great for stringing processes together, provides it's own "flow chart" in the process of doing that, and maybe doing a bit of ETL and the like. But...
It's a pet pieve of mine that some people say that some form of data manipulation should be done in the "application" instead of SQL where the data manipulation is both faster and, many times, causes less network traffic. What I've found is that people use DTS's ability to run VB scripts (and other "things") as a way to get around their lack of knowledge of SQL. Sure, sure... I also believe that you do whatever it takes to get it done but there are many things (like doing CSV splits and the like) that, with a just little forethought or just a little research, are done so much more quickly if you do it in SQL where the data actually lives.
DTS is just another of those "applications" to me...
.Rant complete... regard all further alarms
The only thing I might change for the solution in this thread is that BCP does a lot more checking of the data than Bulk Insert does AND, if you are trying to do it from SQL, the use of BCP requires the use of xp_CmdShell which requires the job to run with "SA" privs. BCP is actually a bit slower than Bulk Insert which does less error checking. And, Bulk Insert does not require "SA" privs... just Bulk Admin privs. Of course, you may need the level of error checking and fact that BCP can be switched to write an error log and save bad records to a separate file and a whole bunch of other neat things.
--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) -
Damn I need to get out more... DBA dream job hunt season officially opens next monday (contract to work on this week-end
).
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply