January 6, 2009 at 1:27 pm
Hi all,
I have a small technical question which I really need all of your expertises and that questions is:
I have created a view and that view based on the table. But I use view to store new data from the users input. How can I link the view and table together in order to allow the new data updated in view where it's also updated in the table too.
How can I do that ? do I have to make a relationship between table and view? or else? and how to do that? would you please show me how? sorry I'm new to this SQL Server 2005
Thank You
January 6, 2009 at 1:41 pm
A view doesn't store data. It's just a saved SQL statement. So, when you change the data in the table, it will immediately reflect in the view, just as it would in any query off the table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 1:43 pm
I'm not totally clear on what you're asking, but it seems to me that you're updating a view and trying to figure out how to get that to update the table the view is querying. Is that correct? If so, you don't need to do anything at all.
Views don't store data. When you update a view, you're actually updating the table, not the view. Views just pull data from tables. When the view is set up following particular rules, you can use it to update the table (or tables) that it queries, but it's actually passing the update through the view to the table, completely automatically.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 2:02 pm
Thank you for the answer
I have another question for all of you, please help me and that question is:
How can I eliminate the times and keep the date only in the table? for example when the user enters their date and that date will store in the table and automatically have the time right after the date.
Thanks
January 6, 2009 at 2:44 pm
This might help you
http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/
If you want to ensure that the data inserted has no time, you'll either need a trigger (which is the more complex and probably slower route), or you'll need to ensure that whatever code does the insert trims the time off. If that's a stored proc, you can use the functions that the blog describes. If it's a front end app, then it depends what language that is in and whether it can be changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 8:17 am
Just to avoid any misunderstanding, you can not store only date without time in a table with DATETIME datatype - what you can do is to modify insert so that it sets the time portion to 00:00:00.000. That means, time will still be stored, but it will be always zero.
However, the capability to store time remains in the column, so you have to make sure that ALWAYS when new row is inserted or date column updated, time portion is set to 0. This is automatical if you insert the date as '20081231', what you have to be careful about are mainly GETDATE() function or dates taken from some other source, where they are stored with time (and - of course - any user input).
January 7, 2009 at 9:37 am
I can't think of any benefit of not storing the time information other than making it slightly easier to write queries against the table and not have to worry about time. Can anyone name an actual benefit to not storing the time? I'd personally store the time and then just drop it when needed in queries.
Having too MUCH data is rarely a problem, too little data on the other hand...
Now if I was using 2K8 data types and had date as an option, then maybe I'd consider this, but taking extra steps to strip time out of a table at point of entry seems silly to me.
January 7, 2009 at 10:35 am
I've had plenty of instances where removing the time was important for a warehouse or reporting solution, but otherwise, I leave it in. I'd rather have it and not need it, than need it and not have it. Doesn't cost any more, in terms of storage space and all that, to have it in there, and removing it from query data at run-time is definitely easy enough.
If I really needed to remove it, I'd be more likely to use a computed column that stripped out the time and keep the original column with the time, then query against the computed column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 9:02 am
Garadin (1/7/2009)
I'd personally store the time and then just drop it when needed in queries.Having too MUCH data is rarely a problem, too little data on the other hand...
I agree, this is the way I'd prefer, too. In fact, not having time stored can bring complications sometimes (you can have lots of ties when you order by date etc.), and ignoring time when you do something with the column is pretty easy. On the other hand, there can be situations where time is absolutely irrelevant and I can understand that people may wish to get rid of it permanently. I wouldn't suggest it myself - but maybe they know why they want to get rid of it (and maybe they don't and will come back after a few months, whining that something doesn't work as expected). Who knows...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply