Jeff Moden

Forum Replies Created

Viewing 15 posts - 49,696 through 49,710 (of 59,099 total)

RE: For loop in sql server

pduplessis (6/25/2008)
Now....
For the SSIS bit.
1) Create connectionmanager to your data
2) Create a preparation SQL task which basically deletes the data, as per the delete statement
3) Create a dataflow. OLEDB source...
--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)

June 25, 2008 at 9:41 pm

#834377
RE: more elegant search

Aw crud... Bill, some of the rows have some commas in the actual data which pretty much screws up "easy". Could you recreate the zip file using Tab delimited...
--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)

June 25, 2008 at 8:38 pm

#834368
RE: more elegant search

Good to see you too... and thanks for the book data in a format that I can actually load easily 😀
Do you have an example set of words that you'd...
--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)

June 25, 2008 at 6:32 pm

#834357
RE: more elegant search

How are the words being passed and do you have a wad of example book titles we can test with? See the link in my signature for the best...
--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)

June 25, 2008 at 5:45 pm

#834348
RE: Handling hirarchy in tables

Rashmi,
How often is the hierarchy going to change?
--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)

June 25, 2008 at 5:41 pm

#834346
RE: Exporting and Versioning Lookup Data: A Real-World Use of the CLR

We have lots of embedded tabs
That would certainly be a fly in the ointment. Thanks for the feedback.
--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)

June 25, 2008 at 5:30 pm

#834345
RE: what date format is this?

Then what in the application is giving you the offset of either 5 or 6 hours? Could it be that all the times are assumed to be GMT and...
--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)

June 25, 2008 at 5:23 pm

#834344
RE: display single column data into multiple clolumns

Stephanie Giovannini (6/25/2008)
Jeff, I like your schema better. Thanks for clarifying how phone numbers and addresses are ideally stored.
Heh... not sure it's the "ideal" way to do it, but I...
--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)

June 25, 2008 at 5:16 pm

#834343
RE: select data and display in different coulumns

Since Oracle 8.3, you also can use Case... 😉
--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)

June 25, 2008 at 5:08 pm

#834341
RE: T-sql question

Good code... but be careful when you get a lot of rows or the groups of rows become large. Take a look at the following article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--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)

June 25, 2008 at 3:48 pm

#834325
RE: concern is correct - not just dba's but developers

R L Reid (6/25/2008)
As a 25 year professional, I've been long concerned about the lack of understanding of relational databases and the systems that they run on - both the...
--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)

June 25, 2008 at 3:36 pm

#834322
RE: Table Valued functions Vs Scalar Valued Functions

The best thing to do, in most cases, it to avoid any kind of function and any kind of correlated sub-query including Cross Apply. Yeah, I know... I'm going...
--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)

June 25, 2008 at 3:29 pm

#834319
RE: Exporting and Versioning Lookup Data: A Real-World Use of the CLR

Very good article... you did a heck of a good job on it. 🙂
Just a couple of observations...
You did all of this just because BCP won't export column headers......
--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)

June 25, 2008 at 3:10 pm

#834306
RE: For loop in sql server

Wow... lots of RBAR there 😀
For starters, you can replace the following WHILE loop...
declare @cnt int
declare @UnitCount int
set @cnt=1
set @UnitCount=1
while @cnt<len(@s_Table_PkgNumber)
begin
if(substring(@s_Table_PkgNumber,@cnt,1)=', ')
begin
set @UnitCount=@UnitCount+1
end
set @cnt=@cnt+1
End
... with this...
SET @UnitCount = LEN(@s_Table_PkgNumber)-LEN(REPLACE(@s_Table_PkgNumber,',',''))+1
That one...
--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)

June 25, 2008 at 7:31 am

#833932
RE: How do I extract Column info for a particular column?

Darned forum... please replace the smiley faces in the code with right parenthesis...
--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)

June 25, 2008 at 7:12 am

#833912

Viewing 15 posts - 49,696 through 49,710 (of 59,099 total)