Database

Let me know if it helps Sample data declare @t table(completeaddress varchar(100)) insert into @t select '1234 McKean Street' union all select '1234 McKean Street' union all select '1234 E McKean Street' union all select '1234 E McKean Street' Query select StreetNum = 'StreetNum : ' + SUBSTRING(completeaddress,1,4) +',' ,Direction = 'Direction : ' + case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W') then SUBSTRING(completeaddress,5,2) else '' end + ',' ,StreetName = 'StreetName : ' + case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W') then SUBSTRING(completeaddress,7,LEN(completeaddress)) else SUBSTRING(completeaddress,6,LEN(completeaddress)) end from @t Output: StreetNum Direction StreetName StreetNum : 1234, Direction : , StreetName : McKean Street StreetNum : 1234, Direction : , StreetName : McKean Street StreetNum : 1234, Direction : E, StreetName : McKean Street StreetNum : 1234, Direction : E, StreetName : McKean Street :) Niladri Biswas

You are always welcome bro :) I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

As Asfield said, join the 2 tables so that you retrieve them once. I would add to what he said is that you probably want to add paging. So instead of processing 1 M record, you can process 1000 record at a time etc..

The INSERT and UPDATE statements have a syntax that can be used to update multiple rows in the destination table from values in one or more source tables. The syntax is different for each database. These are examples for SQL Server: INSERT INTO EmployeeMaster (FirstName, LastName, EmpID) SELECT EmpFirstName, EmpLastName, EmpID FROM EmployeeTemp WHERE (...........) UPDATE EmployeeMaster SET EmployeeMaster.FirstName = EmployeeTemp.EmpFirstName, EmployeeMaster.LastName = EmployeeTemp.EmpLastName, EmployeeMaster.EmpID = EmployeeTemp.EmpID FROM EmployeeTemp T INNER JOIN EmployeeMaster M ON T.EmpID = M.EmpID WHERE (...........)

Actually Wikipedia has a good explaination of Normalization. http://en.wikipedia.org/wiki/Database_normalization[^] Chris Date and Edgard Codd have written numerous books on the topic and are frequently used in Universities as the course textbook. I had to study them when I was in Graduate School.

does not work , but i found a resolution. here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7f24ba5a-b091-4119-9216-1564b61f4aa0/[^] Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Check the link in my reply to the post below. Hope it helps. 50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

A seven field composite primary key X| - sounds like a disaster has found somewhere to happen. This is a BAD idea, I have no knowledge of the performance issues with such a mess but the sheer cumberomeness of forever doing a 7 key join would drive me to drink. I recommend that you insert a primary key into the central table and populate it, then turn autonumber on. Now add the foreign key field to the other table(s) and use the 7 key horror to populate the field. Make your relationship using these fields. You must make sure you support the change within your CRUD code. Never underestimate the power of human stupidity RAH

Mycroft Holmes wrote: I would opt to clean up the database :thumbsup: Although it would be a bit of pain while cleaning the mess but the in the long run, it is going to be very helpful. 50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

I'll be interested in any responses to this one, I have never worked with SQLDependancy. I insist on minimum recordset size for the UI, any more than about 500 records gets looked at severely and usually the design is changed to meet the requirement. Where I have to load a large volume I often allow the user to make a number of changes and then reload the dataset. Never underestimate the power of human stupidity RAH

loyal ginger wrote: There is no DateTime type in SQLite I find that incredible considering all the problems that arise from storing date values as strings. Never underestimate the power of human stupidity RAH

And you got down voted for that answer - FTFY Never underestimate the power of human stupidity RAH

In the Import/Export Wizard make sure that you select the contstraints for data transfer.

Yes David, thanks for your response, the only one so far that is meaningful! I do plenty of flow charting and process mapping so that's already been covered. I do understand our current system inputs/outputs/relations. I think the real questions center around whether there are some products out there that could be used/adapted to this or if not then what type of database structure/model would be recommended. In this case can any examples or samples from this board be referenced?

I just found out that there is SQL Server Options -> Query Execution -> Advanced has the option to set NOCOUNT

From BOL Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. So you used a fuzzy search key word, got a fuzzy return when you wanted a precise return. Wrong tool for the job. Never underestimate the power of human stupidity RAH

Ashfield wrote: he now has enough info to work it out for himself Not with the spoon feeding Niladri had to do, I doubt he has the nous to research cross join! Never underestimate the power of human stupidity RAH

Try this \- Use the backslash character(\) to escape a single character or symbol. Only the character immediately following the backslash is escaped. For example, a query of code\-project matches code-project and code project. or if you don't at all the hyphen(-) try like this [^-] . This will negate the character class. For more information, look here Regular Expression Basic Syntax Reference[^] :) Niladri Biswas