Posted by David Portas on 03/11/06 10:26

pb648174 wrote:
> In a multi-user environment, I would like to get a list of Ids
> generated, similar to:
>
> declare @LastId int
> select @LastId = Max(Id) From TableMania
>
> INSERT INTO TableMania (ColumnA, ColumnB)
> SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
>
> --get entries just added
> SELECT * FROM TableMania WHERE Id > @LastId
>
>
> The above works fine, except I'm assuming it will not work in a
> multi-user environment. Is there any way to get the set of Ids that
> were just added in the previous statement (similar to @@IDENTITY)
> without doing all of this in a serializable transaction or making a
> temp table of every single Id before the insert statement?

This is easy to solve provided you have an alternate key. IDENTITY
should not be the only key of a table and this is one example of why -
without an alternate key you have no entity integrity so you cannot
always guarantee reliable results from the data.

Try the following example. Notice that the reason this works is that
the INSERT list always must include the primary key when you are
inserting multiple rows (otherwise there is no key). The only potential
exception is where you assign a default value that forms part of the
key - for example DEFAULT CURRENT_TIMESTAMP. In that case you need to
retrieve the default value before you do the INSERT so that you can use
it in the SELECT.

CREATE TABLE tablemania (id INT IDENTITY PRIMARY KEY, a INT, b INT,
UNIQUE (a,b));
CREATE TABLE othertable (a INT, b INT, c INT, PRIMARY KEY (a,b,c));

INSERT INTO othertable (a,b,c)
SELECT 1,2,16 UNION ALL
SELECT 1,3,16 UNION ALL
SELECT 1,4,16 UNION ALL
SELECT 1,5,16 ;

DECLARE @t TABLE (a INT, b INT, PRIMARY KEY (a,b));

INSERT INTO @t (a, b)
SELECT a, b
FROM othertable
WHERE c > 15 ;

INSERT INTO tablemania (a, b)
SELECT a, b
FROM @t ;

SELECT T.id, T.a, T.b
FROM tablemania AS T
JOIN @t AS O
ON T.a = O.a
AND T.b = O.b ;

In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
option:

INSERT INTO tablemania (a, b)
OUTPUT inserted.id, inserted.a, inserted.b
SELECT a, b
FROM othertable
WHERE c > 15 ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]