SQLServerPedia

Q: I must create a new identity column in table ‘A’ based on using the Max(rec_id) from table ‘B’ and incrementing by 1 for each subsequent row in table ‘A’. This is so I may append table ‘A’ with table ‘B’ and maintain contiguous (rec_id) numbering. This processing is done within scripts and must be done without intervention…how should I proceed?

Kevin Kline says: I’m not exactly sure what you’re shooting for here. If it were left to me, I’d choose a simpler approach and define Table A as all starting at 1,000,000 and Table B starting at 2,000,000 – or something like that. Then, it’s just a matter of setting the seed value of the IDENTITY attribute on each column. You could then retrieve the data from both columns using:

SELECT * FROM tableA
UNION ALL
SELECT * FROM tableB
GO

And all of the data will come out in one nice, flowing stream except that you’d have a large gap in record numbers from Table A to Table B.

If, on the other hand, you need something like:

Record ID Table of Origin
———– —————–
0001 Table A
0002 Table A
0003 Table B
0004 Table B
0005 Table A
0006 Table B

Then you’ll have to go down a more complex route. In this case, I’d use computed columns. Read about computed columns at http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c06ppcsq.mspx

You could also use a DEFAULT value for the column using a function to return a value every time a row identifier is needed. Note that this approach is likely to be slow. So your application should require a relatively low level of OLTP capabilities.

Hope this helps!

Tags: Programming