Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using TOP To Rank Columns In a Table Expand / Collapse
Author
Message
Posted Tuesday, October 25, 2011 6:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:24 AM
Points: 26, Visits: 232
Yeah, I'm with most others. I find the question more interesting that the solution. Why was it he needed that?
Post #1195690
Posted Tuesday, October 25, 2011 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 27, 2011 8:03 AM
Points: 5, Visits: 7
Have you ever worked up a example to display dynaimc source to destination data column mapppings using the same technique, and also the matching of data items from source column to destination columns, basically a frgament of code that replicates the SSIS data mapper, removing the need to map source to destination in SSIS...?
Post #1195731
Posted Tuesday, October 25, 2011 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 1,706, Visits: 4,850
sanjay.patel 30254 (10/25/2011)
Have you ever worked up a example to display dynaimc source to destination data column mapppings using the same technique, and also the matching of data items from source column to destination columns, basically a frgament of code that replicates the SSIS data mapper, removing the need to map source to destination in SSIS...?

In one of the datamarts I work with, the ordinal position of columns between Dev, QA, and Production are different. What I've done is create a view for each table that gets bulk inserted, and then I reference the view in SSIS or BCP. I've found that to be easier and more maintainable than fooling with the data mapper or bcp format files.
Post #1195743
Posted Tuesday, October 25, 2011 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 27, 2011 8:03 AM
Points: 5, Visits: 7
What I would like to see is a source to destination dynamic data mapper, which is all referenced by using the dynaim columns as in the exmaple you have used, only extended to to also reference a second table, which agin the ordinal positions are sorted to relfect the data mapping, and then having further SQL conditions tagged on to allow a filter to be put in place.

I want two tables, ordinal position to relfect the data mapping sequence, the source and destination columns and condition blocks may exist in either table, a dynamic process to put it all together, just the SQL part, don't want to execute it.....
Post #1195750
Posted Tuesday, October 25, 2011 8:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
thisisfutile (10/25/2011)
I've been coming to these forums long enough to know that Joe Celko was going to respond to this one. I recall Joe discussing the TOP clause in another forum long ago. Correct me if I'm wrong Joe, but TOP isn't to be trusted to pull the "first rows in a table" because tables don't store rows in any particular order. Is that what you were going for in your re-education reprimand? I've always felt it was a little misleading...it should have been called LIMIT or something like that because that's all it's really doing...limiting the result set.

IMHO.

Gabe


I agree TOP can't be trusted to pull the first rows in a table, that's why I included the caveat of ORDER BY Ordinal_Position in the filter.


Post #1195756
Posted Tuesday, October 25, 2011 11:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 587, Visits: 2,552
I am intrigued as to why anyone would need this technique in a normalised database. Was this developer wrestling with a pivot table by any chnce that had been saved as a table?


Best wishes,

Phil Factor
Simple Talk
Post #1195868
Posted Tuesday, October 25, 2011 1:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:52 PM
Points: 56, Visits: 1,046
I'm not going to judge the merits of the problem. But I am going to point out a small but important thing that I see crop up frequently:

 '[' + COLUMN_NAME + ']'


isn't a whole lot better than just COLUMN_NAME - what if they have a ']' in a column name ?

QUOTENAME(COLUMN_NAME,'[')


is much better




Post #1195914
Posted Tuesday, October 25, 2011 6:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
sanjay.patel 30254 (10/25/2011)
What I would like to see is a source to destination dynamic data mapper, which is all referenced by using the dynaim columns as in the exmaple you have used, only extended to to also reference a second table, which agin the ordinal positions are sorted to relfect the data mapping, and then having further SQL conditions tagged on to allow a filter to be put in place.

I want two tables, ordinal position to relfect the data mapping sequence, the source and destination columns and condition blocks may exist in either table, a dynamic process to put it all together, just the SQL part, don't want to execute it.....


Actually, this article gave you enough information to easily write your own. When you're done, you should write an article about it.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1196041
Posted Wednesday, October 26, 2011 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 31, 2011 7:48 AM
Points: 3, Visits: 78
A minor point - the spelling of 'dyanamic'
Post #1196231
Posted Wednesday, October 26, 2011 7:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 1,706, Visits: 4,850
I think the article presents a good enough solution to a specific problem. If I ever have a need to select the TOP X columns from a table, then it would be an ad-hoc query I need right then and there, and I'd rather use someone elses code than waste time rolling my own solution. Obviously it's something that wouldn't belong in the application.
Post #1196244
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse