Set A Field the same as ID (IDENTITY) in the insert

I have a Code (int) in my table, the ID is set开发者_开发技巧 to identity. How can I set a default value for my code to be filled by the same value az ID? I mean Identity.

You could use an after insert trigger:

create table TestTable (id int identity, col1 int)
go
create trigger TestTrigger on TestTable after insert
    as begin
    update  TestTable 
    set     col1 = id
    where   col1 is null
            and id in (select id from inserted)
    end
go

Test code:

insert TestTable default values
insert TestTable (col1) values (666)
insert TestTable default values
select * from TestTable

In general, I try to stay clear of triggers. In the long run using a stored procedure for insert is much more maintainable:

create procedure dbo.InsertTestRow(
    @col1 int)
as
insert  TestTable (col1) values (@col1)

if @col1 is null
    begin
    update  TestTable
    set     col1 = id
    where   id = SCOPE_IDENTITY()
    end

If it always has the same value - why don't you just drop that field. Otherwise it can be maintained with triggers (BEFORE INSERT one).

I'm looking for something in the default value! If it is null it should be filled with the same value as id but if it is provided with some value, it should keep that value

You could solve the issue by using coalesce in your queries instead.

create table T (ID int identity, ID2 int)

insert into T values (default)
insert into T values (null)
insert into T values (78)

select
  ID, 
  coalesce(ID2, ID) as ID2
from T

Result

ID  ID2
--  ---
1   1
2   2
3   78

Assuming your table's ID is an Identity column, you could consider using a constraint:

ALTER TABLE MyTable
ADD CONSTRAINT MyTableCodeDefault
DEFAULT IDENT_CURRENT('MyTable') FOR Code

This works for these use cases:

INSERT INTO MyTable DEFAULT VALUES
INSERT INTO MyTable ({columns NOT including 'Code'}) 
  VALUES ({value list matching insert columns})
INSERT INTO MyTable (Code) VALUES (666)
INSERT INTO MyTable (Code) SELECT 8 UNION SELECT 13 UNION SELECT 21

But it does not work for bulk inserts:

INSERT INTO MyTable ({columns NOT including 'Code'})
SELECT {value list matching insert columns}
UNION 
SELECT {value list matching insert columns}
UNION
SELECT {value list matching insert columns}

This restriction may seem onerous, but in my practical experience, it's rarely a problem. Most of the use cases I've encountered that need a default value involve user/UI 'convenience': don't force the user to pick a value if they don't want to.

OTOH, rarely do I encounter bulk insert situations where it's impractical to specify the value for the columns you're targeting.

You could use computed column, like this:

if object_id('TempTable') is not null drop table TempTable
create table TempTable (Id int identity(1,1), Code as Id)

insert into TempTable
default values

insert into TempTable
default values

insert into TempTable
default values

select * from TempTable

Of course if you have other columns, then you dont need default values:

if object_id('TempTable') is not null drop table TempTable
create table TempTable (Id int identity(1,1), Code as Id, SomethingElse int)

insert into TempTable (SomethingElse)
select 10 union all
select 11 union all
select 12

select * from TempTable

But, like zerkms said - why do you need two columns that are same?

If the field is an Identity field in SQL Server, the database engine will take care of its value. What we normally do is to read the record back (after inserting) to get to the generated Id.

EDIT: It sounds like you are trying to "override" the identity? If so, before you insert, run:

SET IDENTITY_INSERT [tableName] ON

You'll have to be careful not to insert a value that already exists. This can get tricky, though. So maybe consider removing the identity property altogether, and managing the default values yourself?