1

In SQL Server, I have two fields, one is primary and has an identity (AN_ID) then I have another one that for legacy reasons (don't ask) has to be updated reflecting the AN_ID. Currently they have the exact same value. I was wondering if there are pure T-SQL query (no stored procedures) to make it auto incremente.

Thank you very much

Edit: I'm forced to SQL Server 2000

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0plus1
  • 4,475
  • 12
  • 47
  • 89

3 Answers3

4

If both fields can contain the same value, then you can use an insert trigger or a computed column (new in SQL 2005) to accomplish this.

EDIT: Something like this should work.

CREATE TRIGGER MyTable_AI ON MyTable
AFTER INSERT AS
    UPDATE MyTable
    SET OTHER_ID = AN_ID
    WHERE AN_ID IN (SELECT AN_ID FROM inserted);
Jamie Ide
  • 48,427
  • 16
  • 81
  • 117
3

The "right" solution is almost certainly to use a trigger, however if that falls under the heading of "no stored procedures" then you can fudge things a little by running two consective queries (which you should be able to send as one query from your calling app - be useful to know what environment you're using on top of SQL Server).

e.g. (very roughly) 

INSERT INTO tbl (col1, col2, col3) values (@col1param, @col2param, @col3param);
UPDATE tbl SET not_an_id = an_id WHERE an_id = SCOPE_IDENTITY()
Murph
  • 9,985
  • 2
  • 26
  • 41
2

The second column could be a computed column with the formula AN_ID.

You could also create a view with an alias of AN_ID.

Gary W
  • 1,874
  • 1
  • 14
  • 18