Stack Overflow
Stack Overflow

Questions tagged [scope-identity]

120 questions
97
votes
5 answers

SCOPE_IDENTITY() for GUIDs?

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs as our primary keys. Any idea on what the…
bplus
  • 7,177
  • 13
  • 59
  • 83
94
votes
5 answers

Why does select SCOPE_IDENTITY() return a decimal instead of an integer?

So I have a table with an identity column as the primary key, so it is an integer. So, why does SCOPE_IDENTITY() always return a decimal value instead of an int to my C# application? This is really annoying since decimal values will not implicitly…
Earlz
  • 57,517
  • 89
  • 275
  • 484
31
votes
5 answers

Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) Values…
Allbite
  • 2,119
  • 1
  • 21
  • 21
27
votes
1 answer

Is there any way to use SCOPE_IDENTITY if using a multiple insert statement?

I will import many data rows from a csv file into a SQL Server database (through a web application). I need the auto generated id value back for the client. If I do this in a loop, the performance is very bad (but I can use SCOPE_IDENTITY() without…
Thorsten Kraus
  • 267
  • 1
  • 3
  • 3
12
votes
3 answers

How do I cast Scope_Identity() to Int?

So Scope_Identity() returns an ?-Byte Numeric Type in SQL Server. That is not awesome. Is there a safe way to cast it to an int in a select query so we don't have to manage every whim of SQL Server in our ODBC Wrapper?
Peter Turner
  • 10,788
  • 9
  • 62
  • 106
8
votes
4 answers

Is there a way to bulk insert into two tables with FK from one to the other?

I'll give a pseudocode example of my current method and if anyone knows of a method that doesn't work one row at a time, I'd be quite appreciative. I'm using MS SQL Server 2008. define cursor for the data to be inserted (about 3 million…
AppFzx
  • 81
  • 1
  • 3
7
votes
1 answer

How to get Identity of new records INSERTED into table with INSTEAD OF trigger

I am using an INSTEAD OF insert trigger on a table to set an incrementing version number on the row and also copy the row to a 2nd history/audit table. The rows are inserted to both tables without a problem. However, I am having trouble returning…
JumpingJezza
  • 5,122
  • 10
  • 62
  • 98
6
votes
1 answer

SQL Server Profiler showing SCOPE_IDENTITY() while ColdFusion code is not using it in any query

I am using SQL Server 2008 R2 Profiler to debug an issue on a ColdFusion 7 application - that was developed by someone else - running on Windows 7 with SQL Server 2008 R2 as a backend. The application was originally using MS Access 2003 as a backend…
nam
  • 15,516
  • 23
  • 104
  • 228
6
votes
7 answers

Return id after insert C# using SQL Server

I know this question has been on this site many times, but I can't get my code working. I have an Insert statement, and I need the id from that statement on my asp.net page. I'm getting the return value 0. public int newid { get; set; } public void…
Henrik S
  • 105
  • 1
  • 1
  • 6
6
votes
6 answers

SQL Server INSERT, Scope_Identity() and physical writing to disc

I have a stored procedure that does, among other stuff, some inserts in different table inside a loop. See the example below for clearer understanding: INSERT INTO T1 VALUES ('something') SET @MyID = Scope_Identity() ... some stuff go here INSERT…
TheBlueSky
  • 4,326
  • 7
  • 32
  • 61
6
votes
2 answers

SQL Server OUTPUT clause

I am a little stuck with why I can not seem to get the 'new identity' of the inserted row with the statement below. SCOPE_IDENTITY() just returns null. declare @WorkRequestQueueID int declare @LastException nvarchar(MAX) set @WorkRequestQueueID =…
Microsoft Developer
  • 1,731
  • 1
  • 18
  • 27
5
votes
3 answers

What data type does the SQLCommand method ExecuteScalar() return?

In SQL Server, ID is a not null integer, and an identity. When I run the following code, I get an InvalidCastException on the last line: SqlCommand cmd = new SqlCommand(); cmd.Connection = _conn; cmd.CommandText = @"INSERT INTO [Users] (Name,…
Patty
  • 61
  • 1
  • 2
5
votes
2 answers

Will SCOPE_IDENTITY Work in this Case?

I have PK that is self incrementing key. I need to insert the record into the database and then get that PK back and use it in another insert. However I would like to do this in one transaction. Is that possible. The idea is that if something fails…
chobo2
  • 75,304
  • 170
  • 472
  • 780
5
votes
3 answers

How to obtain SCOPE_IDENTITY() from INSERT run in EXEC() statement

I am building a dynamic insert statement within a stored procedure. I build up the sql syntax in a variable and then execute it with EXEC(@VarcharVariable). The SQL insert works fine but when I execute SET @Record_ID = Scope_Identity()…
Jon Leach
  • 761
  • 2
  • 8
  • 21
4
votes
3 answers

T-SQL: returning the new INSERT identity to C#

i'm putting values into SQL Server using a Stored Procedure. The Procedure will add an ID to the row that is added. I need to get this ID back to my code. Currently I can get the I see the output id in the OUTPUT window of Visual Studio, but can't…
Tomasz Iniewicz
  • 4,151
  • 5
  • 37
  • 47
1
2 3 4 5 6 7 8