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 |
|
wtech
Starting Member
11 Posts |
 Posted - 2004-09-04 : 08:29:36
|
| hi,previously i was using an identity column as primary key, now i have changed that, i need to select max of the id and insert a new record with id as max(id) +1. i can write a select statement and then insert but i want to avoid another trip to the server, so i tried it this way: insert into table values ((select isnull(max(eid),0) from table),'wtech'). this is to insert first record, but it is giving error: Subqueries are not allowed in this context. Only scalar expressions are allowed. Can anyone offer a better solutionregards,Wtech |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-04 : 08:59:46
|
| insert into table select (select isnull(max(eid),0) from table),'wtech'But be careful - if two processes do that at the same time they will try to insert the same value (that's why an identity is preferable for this - it makes things simpler).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-04 : 09:07:33
|
| [code]insert #t( eid, txt )select isnull(max(eid)+1,0), 'wtech' as txt from #tinsert #t( eid, txt )select maxeid.eid, 'wetch'from ( select coalesce(max(eid)+1,0) as eid from #t ) maxeid[/code]Why did You drop the identity ?rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-04 : 09:17:44
|
quote:
Originally posted by nr
insert into table select (select isnull(max(eid),0) from table),'wtech'But be careful - if two processes do that at the same time they will try to insert the same value (that's why an identity is preferable for this - it makes things simpler).
Wouldn't select .... from table with(tablock)solve this problem ?rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-04 : 09:48:54
|
| Think it would need tablockx.You need something that blocks the other select. Think tablock wouold just escalate the shared lock for the select.Would slow things down as well.Would need testing. It's justa lot more complicted than an idetity and prone to someone else comming along later and messing it up.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-04 : 09:59:24
|
Yes, probably TABLOCKX, read BOL again  Not using an Identity just complicates things really.In this case I probably wouldn't bother with the locks anyway,just take the VIOLATION OF PRIMARY KEY error if it ever should happen that 2 processes claim the same ID.Depending on business requirements ov course.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
|
|
|
|
|
using select statement in insert