| Author |
Topic |
|
mase2hot
Starting Member
36 Posts |
 Posted - 2010-06-15 : 16:42:34
|
| Hi,ok so I've migrated my access database to server 2008. Now I need a timestamp column in every table. Problem is some tables already have it. What would be the code to insert timestamp column into all tables except where contains column like "stamp" ?Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-15 : 17:11:00
|
| Whn you say "timestamp", do you mean a column with a actual datatype of TIMESTAMP, or do you mean a column with a datatype of DATETIME that contains a date when the row was inserted, updated, etc.Note that a datatype of TIMESTAMP is a special type in SQL Server that has nothing to do with dates and it usually used to simplify optimistic locking.CODO ERGO SUM |
 |
|
|
mase2hot
Starting Member
36 Posts |
Posted - 2010-06-15 : 17:15:35
|
| yes sorry its the latter, so I can keep track of when users in acceess make changes.... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-15 : 17:46:28
|
| [code]SELECT 'ALTER TABLE [' + O.name + '] ADD [MyColumn] datetime NOT NULL CONSTRAINT DF_' + O.name + '_MyColumn DEFAULT GetDate()' + CHAR(13) + CHAR(10) + 'GO'FROM sys.sysobjects AS OWHERE type = 'U' AND NOT EXISTS ( SELECT * FROM sys.syscolumns AS C WHERE C.id = O.id AND C.name = 'MyColumn' )--ORDER BY O.name[/code] |
|
|
|
mase2hot
Starting Member
36 Posts |
Posted - 2010-06-15 : 17:54:54
|
| Wow thanks for that, do I simply replace MyColumn with Timestamp? Not quite sure what 0.name is?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-15 : 18:24:27
|
| Sorry, yes replace "MyColumn" with whatever the name is of the column you have in each table that contains the Create Date + Time.Best to avoid the name "TIMESTAMP" as a column name as it is a SQL Reserved word ... but if that is the name of your existing column then go with that.Then run the script above and it should! give you output that is a script to add columns to those tables that don't already have them.Remove all the system tables etc. (if there are any) or other tables that you don't want to add the column to ... then when you've got it how you want it run it to create the additional columns.Backup the database first! |
|
|
|
mase2hot
Starting Member
36 Posts |
Posted - 2010-06-16 : 04:27:02
|
| Hilittle confused now. The current columns that are time stamp I'm not sure on the exact name it could be Timestamp time_stamp etc.. Thats why I want a script the looks for the name stamp within the column name... If that makes sense? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-16 : 04:55:31
|
[code]SELECT 'ALTER TABLE [' + O.name + '] ADD [Time_Stamp] datetime NOT NULL CONSTRAINT DF_' + O.name + '_Time_Stamp DEFAULT GetDate()' + CHAR(13) + CHAR(10) + 'GO'FROM sys.sysobjects AS OWHERE type = 'U' AND NOT EXISTS ( SELECT * FROM sys.syscolumns AS C WHERE C.id = O.id AND C.name like '%stamp%' )--ORDER BY O.name[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-16 : 05:26:20
|
You write code that looks very much like mine  and save me work want a job?  |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-16 : 05:30:52
|
quote:
Originally posted by Kristen
want a job?
maybe later
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-16 : 06:16:25
|
Oh! I thought you'd started already  |
|
|
|
mase2hot
Starting Member
36 Posts |
Posted - 2010-06-16 : 07:20:36
|
| Thanks that seems to have worked! One thing I changed a value and it didnt update the time stamp should it? Been some time since I last messed aroung with this. Or is the time stamp only for new rows being entered?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-16 : 07:27:25
|
The script only has a DEFAULT set (which will apply to new INSERT records only, and then only if a value is NOT explicitly provided)To change something on UPDATE you need to write a trigger on the table.You can use TIMESTAMP datatype instead of DateTime ... BUT ... despite its name it doesn't store the Date or Time  but rather than unique ID which WILL change whenever the row is Inserted / Updated.If you want Date/Time you have to do that yourself (in a trigger if you want to be sure its done 100% of the time)If you just want a value that changes (so you can easily know that whether a "copy" of the ROW you have is current, or not) then TIMESTAMP will do. ("TIMESTAMP" name has been deprecated in favour of "ROWVERSION" - which describes its use a lot better)( |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-16 : 08:03:18
|
| Wohoo! For the first time ever I can actually refer someone to my blog!! Big moment for me (the blog only has 3 posts so far including a "this is my blog"-post so this was quite a lucky shot )I've made a script on my blog that creates these columns/triggers for you. It actually does quite a bit more but with some minor modification you'll get it to create Created and Modified-columns and make sure that they are updated.-> http://thefirstsql.com/2010/05/21/create-an-audit-table-on-the-fly/- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
Insert timestamp to most tables?