Insert into Table, Max Value from the Same Table

From: Leo J. Hart IV (leo.hart_at_fmr.com)
Date: 11/19/04 

Date: 19 Nov 2004 12:15:08 -0800

Here's the setup:

I have two hypothetical tables:

CREATE TABLE tst_role (
  role_name VARCHAR(10)
)

CREATE TABLE tst_user_role (
  user_name VARCHAR(10),
  role_name VARCHAR(10),
  ins_order_n SMALLINT
)

With tst_role data of:
'Admin'
'Super'
'User'

and no data in tst_user_role.

I want to perform a couple of inserts like this:

DECLARE @user_name VARCHAR(10) SET @user_name = 'Joe'

INSERT
INTO tst_user_role
SELECT @user_name,
         role_name,
         (SELECT ISNULL(MAX(iUr.ins_order_n), 1) + 1
          FROM tst_user_role iUr
          WHERE iUr.user_name = @user_name) as ins_order_n
FROM tst_role

SET @user_name = 'Biff'

INSERT
INTO tst_user_role
SELECT @user_name,
         role_name,
         (SELECT ISNULL(MAX(iUr.ins_order_n), 1) + 1
          FROM tst_user_role iUr
          WHERE iUr.user_name = @user_name) as ins_order_n
FROM tst_role

I would now expect to see the following in the tst_user_role table:

'Joe', 'Admin', 1
'Joe', 'Super', 2
'Joe', 'User', 3
'Biff', 'Admin', 1
'Biff', 'Super', 2
'Biff', 'User', 3

But instead see:

'Joe', 'Admin', 2
'Joe', 'Super', 2
'Joe', 'User', 2
'Biff', 'Admin', 2
'Biff', 'Super', 2
'Biff', 'User', 2

I would think that since this is all happening in the same transaction
the value of MAX(ins_order_n) would change for each new row, but that
doesn't seem to be the case. I know I can get around this using temp
tables and IDENTITY columns, but unfortunately I can't use identity
columns on any permanent tables in our DB (policy) and I would rather
not use a temp table. Any suggestions?

Thanks,
Leo Hart

Relevant Pages

  • Re: Not all users appear in User Accounts
    ... So I created a new Admin userid called TEMP. ... I logged on as TEMP, went into User Accounts, and could see, amongst others, my original "Administrator" userid. ... I'm puzzled why Steve (Admin) sees fewer entries in User Accounts than TEMP. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Programm das automatisch mit Adminrechten
    ... Prefetch in C ... Temp und InternetTemp im Profil ... Die User arbeiten ohne Admin rechte, mir allserdings ist Username und Pw des Admin Accounts bekannt. ... Gibt es eine Möglichkeit ein Progamm automatisch mit diesen Rechten auszustatten um eben genannte Verzeichnisse zu leeren? ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)
  • Re: File Download Save vs Open
    ... AumHa VSOP & Admin; DTS-L.org ... When I click on the docment on www, File Download ... When I choose OPEN the file it is saved in Temp folder ... as a .tmp file and opened in MS Word, but then I am trying to Sent To ...
    (microsoft.public.windows.inetexplorer.ie6.browser)
  • Programm das automatisch mit Adminrechten
    ... Temp und InternetTemp im Profil ... Die User arbeiten ohne Admin rechte, mir allserdings ist Username und Pw des Admin Accounts bekannt. ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)