| Author |
Topic |
|
cardgunner
326 Posts |
 Posted - 2007-12-12 : 10:32:04
|
| Hi all, I want to create a new field into a table. I have NEVER done this before. In order to create a new field(daterec) into an existing table(model)i would have to do what? This field(daterec) is a date field. There are existing records in this table so I would also have to set the new field to accept null or empty values? Or assign a value to the existing records. (which is better or worse?)I also want this field to be set to current_timestamp. So when I new record is made, the date and time that is was created will be stored without any user input. Make sense??Insert into model (daterec)set current_timestamp?Any help our guidance would be great!Card Gunner |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-12 : 10:34:17
|
alter table MyTableadd DateRec DATETIME default (CURRENT_TIMESTAMP)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
cardgunner
326 Posts |
Posted - 2007-12-12 : 10:38:56
|
| That's it?I thought there would be more.So this would create a new field into the existing table?This would have that new field record date records?This would record when the record was created?And I don't have to do anything to the records that are there, unless I can figure out when they where created and then I would have to update it?If so Great! Thank you.Card Gunner |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-12 : 10:42:51
|
quote:
Originally posted by cardgunner
So this would create a new field into the existing table?This would have that new field record date records?This would record when the record was created?And I don't have to do anything to the records that are there, unless I can figure out when they where created and then I would have to update it?
Yes.Yes.Yes.Yes.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
cardgunner
326 Posts |
Posted - 2007-12-12 : 10:44:40
|
| WELL FANTASTIC!Thank you.I hope you have a great New Years and a Merry Christmas.Card Gunner |
|
|
|
cardgunner
326 Posts |
Posted - 2007-12-15 : 13:16:30
|
| Second question a little late in the game.Is there a way to insert that field before a field or after a field.Meaning I did this on a test table and it inserted the field at the end of the table. For reasons of ??? I'm being asked to insert that in a specific spot in the table, possible?Card Gunner |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 13:47:34
|
to do that you have create a new table with the schema you want, then populate it from the original table.
elsasoft.org |
|
|
|
cardgunner
326 Posts |
Posted - 2007-12-15 : 14:10:33
|
| Well that stinks.To make sure I understand, I would have to create a new table transfer the info from one table to the next and then rename the new table.Card Gunner |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-15 : 17:36:51
|
Another option is to add the column at the end, rename the table, and then create a view with the former name of the table that returns columns in the order your boss wants. I think the requirement that the column order of a table be a certain way is a bit silly. that's an implementation detail that should be invisible to clients. If it's because you have clients doing select * from this table, then tell them that's why they shouldn't use select *.
elsasoft.org |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-17 : 03:31:20
|
| Also in front end application always use Rs("colname1"), Rs("colname2"),etc instead of Rs(0),Rs(1),etc where Rs is the recordsetMadhivananFailing to plan is Planning to fail |
|
|
|
|
Insert Into Table