0

Is there a way to automatically copy an auto incremented identity int column into another column? 

create table dbo.CopyOfIdColumn
(
myIdCol int identity(1,1)
,copyOfMyIdCol int Default (I WANT THE DEFAULT TO BE WHATEVER THE VALUE OF myIdCol IS)
)

I know I can wrap in a stored procedure and just update the entry based on the inserted record using something from Best way to get identity of inserted row? post, probably SCOPE_IDENTITY(), but I'm hoping for a simpler/easier way.

Thanks in advance.

UPDATE: Per Panagiotis Kanavos's suggestion, updating the question to better indicate what I want to achieve.

What I want to do is have a column that indicates the parent for a row (often itself). I would prefer for the column to not have null values.
I also don't want an extra table. Parents and children have the same data and I don't want to have a table with just parentIds. His suggestion is that I can achieve this with a Default constraint.

vbp13
  • 1,040
  • 1
  • 10
  • 20
  • Sounds like an XY problem. What is the actual problem you want to solve here? There's no reason to duplicate the value here, and if you really did want it twice, just return the value twice in your `SELECT` : `SELECT myIdCol, myIdCol AS copyOfMyIdCol,...`. – Thom A Oct 30 '19 at 14:27
  • Yes. I can avoid it if I know it's not possible. I have rows that may or may not have children, Id, ParentId. Yes. I can have two tables (parents table) and (children, if any, table). IMO, it's cleaner to have one table. The data is the same for parents and children. I don't want to have a table with just parentIds and no other data. – vbp13 Oct 30 '19 at 14:31
  • 1
    Update the question and explain what you really want to do. You can easily create a DEFAULT constraint that references the other column. You don't need `SCOPE_IDENTITY()` to return inserted valuse, the OUTPUT clause is a far better option. As for ID/ParentID, a) ParentID should *not* be identical to some other value otherwise you won't be able to use recursive queries to walk the hierarchy and b) `hierarchyid` is better and far faster than `ID/ParentID` – Panagiotis Kanavos Oct 30 '19 at 14:33
  • 2
    So is `copyOfMyIdCol` actually `ParentID`? If a row doesn't have a parent, then the value should be `NULL`. – Thom A Oct 30 '19 at 14:33
  • Reasonable. It is definitely a possible workaround that I will consider if it is indeed not possible. – vbp13 Oct 30 '19 at 14:36

2 Answers2

0

You can create a sequence and then use it in the inserted row, something like:

CREATE SEQUENCE Schema.Sequence
    AS int  
    INCREMENT BY 1 ;

And your inseret would looks like:

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Schema.Sequence;  
-- Some work happens  
INSERT Test.Orders (OrderID, ColumnWithID, Name, Qty)  
    VALUES (@NextID, @NextID, 'Rim', 2) ;  
GO
Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
0

use calculated fields as below:

create table #temp
(
myIdCol int identity(1,1),
othercolumn varchar(20),
copyOfMyIdCol as myIdCol 
)

--TESTING
insert into #temp (othercolumn)
values('a'),('b')


select * from #temp
Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20