MS SQL Server - SELECT MAX(MyNumber) + 1 FROM MyTable is always 1

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

SELECT MAX(MyNumber) + 1 FROM MyTable is always 1

DraguVaso

2005-10-20, 8:23 pm

Hi,

I'm trying to migrate a table from one database to a new version, but the
table changes somehow. The new table has a column which must be kind of a
numbered ID.

I tried it like this:
INSERT INTO PRESTATAIRE2 (
Field1,
NUM_MATR)
SELECT
Myfield AS Field1,
(SELECT (COALESCE(MAX(NUM_MA
TR), 0) + 1) FROM LGDP..PRESTATAIRE2)
AS NUM_MATR,
FROM Xdb..SSRFACC

The problem is: the result gives me all my NUM_MATR = 1, but they should be
1, 2, 3, 4, 5, ....

This numbering must restart for each different type of record, so I can't
add an Identity-column to it: Actually: I can't change my new Table at all.

Does anybody knows a way how to get for each inserted record an incremented
number?

Thanks a lot in advance,

Pieter

Sponsored Links