Tuesday, March 27, 2012

Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

No comments:

Post a Comment