Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-04-08 : 04:44:01
|
| I am trying to work out an acceptable way of doing something and would appreciate comments/suggestions.I have a large table of a national dataset, each element of which has a national code. I don't want to put this into my system as a whole as it causes performance issues and much of the dataset would never be used.My application has a smaller dataset, in a table, based on the national dataset but with the addition of a local code.I am on occasion going to have to pull individual records from the national dataset into the application dataset on an ad hoc basis. My problem here is with the local code. I already have some VBA that generates the local code (it was originally done in MS Access.)The local code is devised as followsTake the name from the national dataset (e.g. BLOGGS, JOE)Change the Name to initial caps (e.g. Bloggs, Joe)Remove all non alphanumeric characters from the name (e.g. BloggsJoe)If the name is now more than n characters, then just take the first n characters (e.g. BloggsJo)Count the number of entries with the same name and add one to that number, then append the number to the name (to create a unique local code) (e.g. BloggsJo28)My question is this. Would I be better off 'translating' my VBA into SQL (bearing in mind that I am using SQL Server 7 (on NT4) - so no UDF's etc.) or would I be better turning the VB I have into something that SQL server can use and if so what are my options and how could it be called? OR would I be better using DTS (which I'm reasonably familiar with (sorry guys!) as I can then convert by VBA to vbscript) OR is there an other option(s) I haven't consideredThanks in advancesteveA sarcasm detector, what a great idea. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-08 : 13:22:09
|
| If you are doing it in VB you are having to do it row-by-row, right? Whereas in SQL you could do it for all rows (the Mixed Case is a little trickey, is that important? Unless your SQL is Case Sensitive then mixed/upper is not ging to matter).SQL could update all the rows, and assign the Local Code, in one Update statement.Kristen |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-04-11 : 03:16:59
|
| Yes, you're right VB is doing it row by row. Having said that these are going to be generated individually on an ad hoc basis The mixed case is important because that's what the users see in the app!!! (not my database so I take no responsibility on that front).It looks to me as though translating to SQL is going to be my 'best' option at the moment due to time pressuresThanks KristensteveA sarcasm detector, what a great idea. |
|
|
|
|
|
|
|
|
Suggestions please