Stack Overflow
Stack Overflow

Questions tagged [scope-identity]

122 questions
104
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…
handles
  • 7,639
  • 17
  • 63
  • 85
101
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
  • 62,085
  • 98
  • 303
  • 499
38
votes
6 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,367
  • 1
  • 24
  • 22
30
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
  • 315
  • 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
  • 11,199
  • 10
  • 68
  • 109
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,498
  • 11
  • 67
  • 106
7
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
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
  • 21,967
  • 37
  • 158
  • 332
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
  • 5,526
  • 7
  • 35
  • 65
6
votes
1 answer

Get SCOPE_IDENTITY value when inserting bulk records for SQL TableType

I have following table structure, for convenience purpose I am only marking individual columns Table_A (Id, Name, Desc) Table_1 (Id this is identity column, Name....) Table_2 (Id this is identity column, Table_A_Id, Table_1_Id) The relationship…
Mr. Imish
  • 84
  • 2
  • 6
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,919
  • 1
  • 20
  • 27
6
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
  • 821
  • 2
  • 10
  • 21
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
  • 83,322
  • 195
  • 530
  • 832
1
2 3
…
8 9