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 |
|
daidaluus
Yak Posting Veteran
73 Posts |
 Posted - 2007-12-12 : 05:37:52
|
| I have a table with an id column which is not defined as IDENTITY. Every time that I want to insert into this table I should calculate MAX of id and increment this value for each inserted row, much like a real IDENTITY columns. For some reasons I cant set it as IDENTITY. I want to do something like this:@id = Get Max IdINSERT INTO Tbl (id, col1, col2, ...)SELECT IncrIdForEachInsertedRow(@Seed=@id+1), col1, col2, ...FROM AnyTblWHERE ConditionI had done it with temporary table. Is there any way to do it in a single insert statement? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-12 : 06:02:48
|
INSERT TargetTable (PkCol, Col1, Col2)SELECT COALESCE((SELECT MAX(PkCol) FROM TargetTable), 0) + 1, Col1, Col2FROM SourceTable
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
daidaluus
Yak Posting Veteran
73 Posts |
Posted - 2007-12-12 : 06:10:21
|
| It works if just one row will be inserted. for more-than-one-rows inserts it repeats that MAX+1 for all newly inserted row. I look for a way that any new inserted row have its own id. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-12 : 12:22:45
|
| Not sure it works but you can try writing a INSTEAD OF trigger on table for insert and set value of id column as MAX(pk)+1 inside this. But this will surely have a performance hit. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-12-12 : 13:37:16
|
| Can you put an IDENTITY on the temp table and then use that ID to add to the MAX ID from the target table? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Like IDENTITY