Как мне вернуть новое значение столбца IDENTITY из инструкции SQLServer SELECT?

Я вставляю в таблицу SQLServer с автоматически увеличивающимся ключевым полем. (Я считаю, что это называется столбцом IDENTITY в SQLServer.)

В Oracle я могу использовать ключевое слово RETURNING, чтобы дать моему оператору INSERT набор результатов, как запрос SELECT, который вернет сгенерированное значение:

INSERT INTO table
(foreign_key1, value)
VALUES
(9, 'text')
RETURNING key_field INTO :var;

Как мне сделать это в SQLServer?

Бонус: хорошо, пока что хорошие ответы, но как, если возможно, объединить их в одно утверждение? :)


sql sql-server tsql identity auto-increment
person skiphoppy    schedule 10.06.2009    source источник

Ответы (7)


arrow_upward
17
arrow_downward

В общем, это невозможно сделать в одном заявлении.

Но SELECT SCOPE_IDENTITY () может (и должен) размещаться непосредственно после оператора INSERT, поэтому все это делается в одном вызове базы данных.

Пример:

mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");

Вы можете использовать OUTPUT, но у него есть некоторые ограничения, о которых вы должны знать:

http://msdn.microsoft.com/en-us/library/ms177564.aspx

person richardtallent    schedule 10.06.2009
comment
Невероятный. Я понятия не имел, что могу комбинировать подобные утверждения. Умный, нестандартный, отвратительный, полезный ... все эти прилагательные приходят на ум. :) - person skiphoppy; 10.06.2009
comment
Это можно сделать одним оператором в SQL Server 2005 с OUTPUT - person gbn; 10.06.2009

arrow_upward
9
arrow_downward 

SELECT SCOPE_IDENTITY()

Изменить: играть ...

Если бы только предложение OUTPUT поддерживало локальные переменные.

В любом случае, чтобы получить диапазон идентификаторов, а не синглтон

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))

INSERT @Mytable (valuecol) 
OUTPUT Inserted.keycol
SELECT 'harry'
UNION ALL
SELECT 'dick'
UNION ALL
SELECT 'tom'

Редактировать 2: за один звонок. Мне ни разу не довелось использовать эту конструкцию.

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))

INSERT @Mytable (valuecol) 
OUTPUT Inserted.keycol
VALUES('foobar')
person gbn    schedule 10.06.2009
comment
Я поддерживаю это. SCOPE_IDENTITY () возвращает самый последний идентификатор в рамках вашего последнего запроса. Другие методы @@ IDENTITY и IDENT_CURRENT () не ограничены определенной областью действия. - person James Conigliaro; 10.06.2009

arrow_upward
4
arrow_downward

Помимо @@ IDENTITY, вам также следует изучить SCOPE_IDENTITY () и IDENT_CURRENT (). Скорее всего, вам понадобится SCOPE_IDENTITY (). У @@ IDENTITY есть проблема, заключающаяся в том, что он может вернуть значение идентификатора, созданное в триггере на фактической таблице, которую вы пытаетесь отслеживать.

Кроме того, это однозначные функции. Я не знаю, как работает ключевое слово Oracle RETURNING.

person Tom H    schedule 10.06.2009

arrow_upward
2
arrow_downward

SCOPE_IDENTITY

person Ken Burkhardt    schedule 10.06.2009

arrow_upward
2
arrow_downward

Это зависит от контекста вашего вызова.

Если вы вызываете это из клиентского кода, вы можете использовать OUTPUT, а затем прочитать возвращаемое значение.

DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))

INSERT INTO @t (ColStr)
OUTPUT Inserted.ColID
VALUES ('Hello World')

Результат:

      ColID
-----------
          1

Если вы обертываете это в хранимой процедуре, использование OUTPUT требует больше работы. Здесь вы захотите использовать SCOPE_IDENTITY (), но вы не можете сделать это с помощью одного оператора. Конечно, вы можете поместить несколько операторов в одну строку с помощью символа ';' разделитель, но это не единичный оператор.

DECLARE @idValue    int
DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))

INSERT INTO @t (ColStr) VALUES ('Hello World')

SELECT @idValue = SCOPE_IDENTITY()

Результат: переменная @idValue содержит идентификационное значение. Используйте параметр OUTPUT, чтобы вернуть значение.

person Rob Garrison    schedule 10.06.2009

arrow_upward
0
arrow_downward

Вы можете использовать OUTPUT INTO, который дает дополнительные преимущества, заключающиеся в возможности для захвата нескольких вставленных удостоверений.

person Cade Roux    schedule 10.06.2009

arrow_upward
0
arrow_downward

INSERT INTO table (foreign_key1, value) VALUES (9, 'text'); SELECT @@ IDENTITY;

person Bhaskar    schedule 10.06.2009