Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
contec
Starting Member
9 Posts |
 Posted - 2006-11-23 : 16:46:31
|
| Hi,I'm trying to insert records from one table into another table. The destination table has a ROWID field which cannot be an identity key, but needs to 'act like' an identity key and have its value populated with (Max(ROWID) + 1) for each row added to the table.To my thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not work as it will only be evaluated once so if I am adding 1000 records and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of 1235.Is there a way to accomplish this?Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-23 : 21:05:06
|
| Insert your data into a temp table with an identity column first to generate a sequence number for each row, and then insert into the destination table with (Max(ROWID)+TempID)CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-24 : 04:55:55
|
| We sometimes use a table of "next available number" for this purpose. The benefit of this is that if I have 10 rows, say, to insert I can increment the "Next available number" table by 10, and then use those 10 numbers for my insert. Which is more efficient than doing a MAX() for each row in turn.Might help to know why your number can't be an Identity?Kristen |
|
|
|
|
|
|
Consecutive numbering WITHOUT identity