SQL Automatically Date and Time Stamp New Rows


You may to date and time stamp every row/record that you add to your table. You may want this for auditing purposes or to make it easier to find records in the future by sorting them by datetime. You can create a non-nullable DATETIME column on your table, and create a DEFAULT constraint on it to auto populate when a row is added. You can use the GETDATE() function to get the date from the system that the query is running on.

Below is a screenshot of SQL Server Express in the Table Design.

While we are on this topic we can also add an sequence number column to our History table that increments by one and starts with one. Here is the screen shot of SQL Server in the Table Design mode again.

But you can’t do this so easily. You need to DROP and CREATE To in the Script Table as option in the popup when you right click. You will lose all of your data if you do this without backing up your data to another table, however. I created a table called History2 with the script. At the bottom I added the second ALTER TABLE line followed by GO.

USE [EFTHistory]
GO
ALTER TABLE [dbo].[History] DROP CONSTRAINT [DF_History_DateCreated]
GO
/****** Object:  Table [dbo].[History]     ******/
DROP TABLE [dbo].[History]
GO
/****** Object:  Table [dbo].[History]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[History2](
	[FirstName] [varchar](10) NOT NULL,
	[Amount] [int] NOT NULL,
	[UniqueNumber] [varchar](10) NOT NULL,
	[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[History2] ADD  CONSTRAINT [DF_History_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[History2] ADD id INT IDENTITY(1,1) 
GO

If the data in the New table is coming from differnt sources, such as differnet clients, we may want to add another column to identify the source,

Leave a comment

Your email address will not be published. Required fields are marked *