Inserting rows into a table with one IDENTITY column only [duplicate]

by

If you have one column that is an IDENTITY, just do this

INSERT MyTable DEFAULT VALUES;  --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();

If you don’t have identity, then can you set it? This is the best way.. and use the SQL above.

If not, you want to insert a new row

INSERT MyTable (admidid)
OUTPUT INSERTED.admidid --returns result to caller
SELECT ISNULL(MAX(admidid), 0) + 1 FROM MyTable

Notes:

  • Under high loads the MAX solution may fail with duplicates
  • SCOPE_IDENTITY is after the fact, not before
  • SCOPE_IDENTITY only works with an IDENTITY column. Ditto any idiocy using IDENT_CURRENT
  • The output clause replaces SCOPE_IDENTITY for the MAX solution

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server – How to lock a table until a stored procedure finishes
  4. What is a stored procedure?
  5. How to assign an exec result to a sql variable?
  6. SQL Server SELECT into existing table
  7. How to check if a stored procedure exists before creating it
  8. Stored procedure that exports data into csv files only exports to one file
  9. Eliminate and reduce overlapping date ranges
  10. How to create a specific SQL Server stored procedure? [closed]
  11. SQL Server dynamic PIVOT query?
  12. SQL Server – Best way to get identity of inserted row?
  13. Should I use != or for not equal in T-SQL?
  14. How to update two tables in one statement in SQL Server 2005?
  15. SQL Server Process Queue Race Condition
  16. Is the NOLOCK (Sql Server hint) bad practice?
  17. SQL – Subtracting a depleting value from rows
  18. Stored procedure or function expects parameter which is not supplied
  19. How to set variable from a SQL query?
  20. Compare DATETIME and DATE ignoring time portion
  21. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  22. SQL dot notation
  23. Split function in SQL Server 2008
  24. T-SQL How to create tables dynamically in stored procedures?
  25. What is the equivalent of ‘describe table’ in SQL Server?
  26. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  27. How do I compare 2 rows from the same table (SQL Server)?
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment