How to imitate IDENTITY behavior with another column?

Question

I created a table with a column that represents the row's insertion date:

create table foo
(
    id int not null primary key identity,
    bar varchar(50),
    insertion_date datetime default current_timestamp
);

I use the following statement to insert a new row:

insert into test (foo) values ('placeholder');

What I want to accomplish is get the behavior of id column in insertion_date column, so it don't have to be specified in insert statement, something like that:

insert into test values('placeholder');

Thanks in advance

Answer 1

To the best of my knowledge one cannot get exactly what you are asking for, but one can insert against a view.

CREATE VIEW fooView AS 
SELECT id, bar
FROM dbo.foo;
GO
INSERT dbo.fooView VALUES ('baz');
GO

The downside is that two different names would be needed for insert and any use that requires insertion_date.

Personally I would not approve a pull request (code review) that did the above to avoid specifying columns on insert.