[MSSQL]Insert row with non-auto-incrementing unique field?

LazyManc

LazyManc

LazyManc

Associate
Joined
21 May 2003
Posts
1,365
I'm currently working on a new project which is interfacing with someone elses MSSQL database, where I have INSERT / UPDATE / SELECT / DELETE permissions on a table but no ALTER table permissions. I haven't used MSSQL before (all my previous work has been on MySql) so please excuse any glaring holes in my knowledge.

I'm trying to insert a new account into the "accounts" table. The problem is, it's got an "account" field which is set as a unique value and stores the account number, but it's not set to auto-increment. Now if this was my database, i'd call the field "id" and set to auto-increment, but unfortunately I don't have this option.

Is there an SQL keyword I can use to say "insert as next row" rather than having to run an extra query first to find the highest "account" number and then adding 1 before inserting the new row?
 
There isn't any magic keyword no.

If you only want to insert one record at a time (insert one row per sql statement), then you can do some cunning sub-selects to get the highest number and then add one to it.

I don't think there's an easy way of doing it for multiple rows in a single statement though.
 
Code: 
INSERT INTO Accounts (Account, Field1, Field2, ...)
SELECT MAX(Account) + 1 @Field1, @Field2, ...
FROM Accounts

I think that is about the best you'll be able to manage, and it only works for one row at a time. You could cope with multiple rows at once by using a temporary table with an auto incrementing field, then applying an offset to it, but personally I'd try to get an auto increment set up as they are a deal more reliable and efficient, though I understand if that isn't possible :)

arty
 
You must log in or register to reply here.
Back
Top Bottom