SQL Server - повернення значення після INSERT

Я намагаюся отримати значення key-value після вставки INSERT-statement. Приклад: У мене є таблиця з ім'ям і ідентифікатором атрибутів. id - породжене значення.

    INSERT INTO table (name) VALUES('bob');

Тепер я хочу повернути ідентифікатор на тому ж кроці. Як це зроблено?

Ми використовуємо Microsoft SQL Server 2008.

211
додано Автор Vladimir Vagaytsev, джерело
Я знайшов корисну відповідь тут: [preparedstatement-with-statement-return-generated-keys] [1] [1]: stackoverflow.com/questions/4224228/ …
додано Автор Lars Ladegaard, джерело

10 Відповіді

Немає необхідності в окремому SELECT ...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

Це працює для стовпців без ідентичності (наприклад, GUID)

343
додано
Привіт, ти подав на SO/SE? Ваш останній пост був датований Dec'14. З тих пір тільки коментарі.
додано Автор abatishchev, джерело
@JonnyLeeds: ви не можете це зробити для змінної (за винятком змінної таблиці). OUTPUT переходить до клієнта або столу
додано Автор gbn, джерело
@ hajikelist: це зовсім краєвий випадок, зазвичай SET NCOOUNT ON у тригері допомагає. Див stackoverflow.com/questions/1483732/set-nocount-on-usage
додано Автор gbn, джерело
Ніколи не використовуйте @@ IDENTITY. SCOPE_IDENTITY, так, але ніколи не @@ IDENTITY. Це ненадійно
додано Автор gbn, джерело
Чи могли б ви детально розібратися? Де виводиться в цьому прикладі? Документація лише показує приклади для таблиць (використовуючи вихід ... в ) В ідеалі я хотів би просто передати це в змінну
додано Автор Jonny Leeds, джерело
Ви також можете використовувати @@ IDENTITY та інші. І пункт замовлення має значення! (знайшов жорсткий шлях ..)
додано Автор Z. Khullah, джерело
@ gbn Я не погоджуюсь - тригери надзвичайно поширені. Я не впевнений, що настройка NOCOUNT виправляє це, будь ласка, прочитайте це повідомлення msdn для більш глибокого пояснення.
додано Автор hajikelist, джерело
На жаль, ви не можете покладатися на це, оскільки додавання тригера до таблиці порушить ваші заяви! re: blogs.msdn.com/b/sqlprogrammability/archive/2008/07/11/ …
додано Автор hajikelist, джерело

Використовуйте SCOPE_IDENTITY() , щоб отримати нове значення ідентифікатора

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()

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

126
додано
@ liho1eye - ОП вказав назву колонки ідентифікатора як id , так що так.
додано Автор Curt, джерело
припускаючи, що id є ідентифікацією
додано Автор Ilia G, джерело
Що стосується більшої системи, то що, якщо багато SQL запускатимуться одночасно? Чи буде він повернути останній вставлений ідентифікатор до кожного запиту?
додано Автор Shiv, джерело
@ Shiv "SCOPE_IDENTITY повертає значення, вставлені лише в межах поточної області"
додано Автор goodies4uall, джерело
INSERT INTO files (title) VALUES ('whatever'); 
SELECT * FROM files WHERE id = SCOPE_IDENTITY();

Є найбезпечнішою ставка, оскільки існує відома проблема з конфліктом статей OUTPUT на таблицях з тригерами. Робить це цілком ненадійним, навіть якщо у вашої таблиці в даний час нема ніяких тригерів - хтось, що додасть один вниз, порушить вашу програму. Час бомби сорт поведінки.

Для більш глибокого пояснення дивіться статтю msdn:

http : //blogs.msdn.com/b/sqlprogrammability/archive/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults.aspx

30
додано
@hajikelist Всі ми маємо спадщину, але ризик викликів, що перешкоджають виходу OUTPUT, є низьким, все, що потрібно, встановлено на nocount. Якщо хтось додасть тригер, то вони повинні знати, як його кодувати (мається на увазі, що у вас є керування в основному), або вам потрібно навчити ваших розробників. В якийсь момент ви будете змушені мігрувати, коли ця версія SQL більше не буде підтримується тощо, тому тригери не призведуть до результату. Що б не було, це не найкраща відповідь, тому що якщо у вас є INSTEAD OF тригерів, SCOPE_IDENTITY може не працювати ( stackoverflow.com/questions/908257/ … )
додано Автор gbn, джерело
Тільки якщо ви не додасте SET NOCOUNT ON у тригери. Також див. docs.microsoft.com/en-us/sql/database-engine/configure-windo‌ ws/& hellip;
додано Автор gbn, джерело
це не є варіантом для наших застарілих середовищ @ gbn
додано Автор hajikelist, джерело
@ gbn - я просто хочу уникати дурних речей, як це. Я не збираюсь розповідати всім своїм розробникам: "Не забудьте додати" не порушуйте мій опис програми "у кожному тригері". - Ви можете зберегти це. Сценарій "замість" є набагато кращим варіантом IMO.
додано Автор hajikelist, джерело

Entity Framework виконує щось схоже на відповідь gbn:

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO Customers(FirstName)
OUTPUT inserted.CustomerID INTO @generated_keys
VALUES('bob');

SELECT t.[CustomerID]
FROM @generated_keys AS g 
   JOIN dbo.Customers AS t 
   ON g.Id = t.CustomerID
WHERE @@ROWCOUNT > 0

Результати виводу зберігаються у тимчасовій змінній таблиці, а потім знову повертаються до клієнта. Треба знати про готча:

вставки можуть генерувати більше одного рядка, тому змінна може мати більше одного рядка, тому ви можете повернути більше одного ID

Я не маю жодної ідеї, чому внутрішня еффектна приєднання ефемерного столу до реального столу (за яких обставин ці два не збігаються).

Але це те, що EF робить.

SQL Server 2008 або новіший тільки. Якщо це 2005 рік, то вам не повезе.

12
додано

@@ IDENTITY - це функція системи, яка повертає останню вставлену ідентичність.

7
додано
Необхідно не рекомендувати ніколи використовувати @@ IDENTITY - це не точне (занадто широке) набагато менш безпечне для потоку - будь-ласка, див. Відповідь @ Curt про SCOPE_IDENTITY ().
додано Автор zanlok, джерело

After doing an insert into a table with an identity column, you can reference @@IDENTITY to get the value: http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx

5
додано
так, ніколи не користуйтеся це не завжди добре працює: X
додано Автор H.Ghassami, джерело
Ніколи не використовуйте @@ IDENTITY: це не сфера безпеки: тригери тощо впливають на нього.
додано Автор gbn, джерело

Ви можете використовувати scope_identity, щоб вибрати ідентифікатор рядка, який ви тільки що вставили в змінну, тоді просто виберіть потрібні стовпці з цієї таблиці, де ідентифікатор = ідентифікатор, який ви отримали від scope_identity

See here for the MSDN info http://msdn.microsoft.com/en-us/library/ms190315.aspx

3
додано

* Parameter order in the connection string is sometimes important. * The Provider parameter's location can break the recordset cursor after adding a row. We saw this behavior with the SQLOLEDB provider.

Після того, як рядок буде додано, поля рядка недоступні, БЕЗПЕЧЕННЯ, якщо Провайдер вказано як перший параметр у рядку з'єднання. Якщо постачальник знаходиться в будь-якому місці рядка з'єднання, крім як перший параметр, нові вставлені поля рядків недоступні. Коли ми перенесли Постачальника до першого параметра, поля рядків магічно з'явилися.

2
додано
Вам потрібно відредагувати і покращити свою відповідь. В даний час це шумно і не зустрічається як гідна відповідь або навіть спроба
додано Автор James, джерело
Мабуть, на цю сторінку з'явилось багато користувачів, тому що вони не мали дійсних полів, щоб визначити щойно додану рядок. Ця поведінка ми виявили (що просто змінюючи порядок параметрів у рядку з'єднання, миттєво отримуємо доступ до нещодавно доданого рядка) настільки химерно, що я вважав за потрібне згадати про неї, особливо тому, що це, швидше за все, виправдатиме причину того, що люди хочуть нову ідентифікатор рядка та інші поля цього рядка. Просто перенесення провайдера як першого параметра, проблема зникає.
додано Автор David Guidos, джерело
Що саме ви маєте на увазі під "шум"? Вам потрібно пояснити вашу скаргу. Це приблизно так просто, як це можливо. Якщо ви змінюєте порядок параметрів у вашому рядку з'єднання, це може вплинути на те, чи доступні дані рядка після вставки.
додано Автор David Guidos, джерело
Не могли б ви сказати нам, як цей коментар відповідає/відповідає питанню? Я не відчуваю, що це заслуговує капелюшок/напівжирний. Якщо ваша відповідь вважається корисною, користувачі проголосують за це.
додано Автор n__o, джерело

Ви можете додати виписку для заяви до вставки. Integer myInt = Вставити в таблиці 1 (FName) значення ('Fred'); Виберіть Scope_Identity (); Це поверне значення ідентичності при виконанні масштабування.

0
додано

Ось як я використовую OUTPUT INSERTED, вставляючи до таблиці, яка використовує ідентифікатор як ідентифікаційний стовпець у SQL Server:

'myConn is the ADO connection, RS a recordset and ID an integer
Set RS=myConn.Execute("INSERT INTO M2_VOTELIST(PRODUCER_ID,TITLE,TIMEU) OUTPUT INSERTED.ID VALUES ('Gator','Test',GETDATE())")
ID=RS(0)
0
додано