How do I get the next auto increment value for a SQL table and use it?

Question

I have linked a basic database containing 2 tables, Login and Customer:

Login contains (CustomerID, loginID, password)
Customer contains (CustomerID, name, last, dob)

CustomerID is primary key in both fields, and is the auto-increment value in the Login table.

When I want to register a new user, I want to FIRST populate Login table (which has CustomerID as an auto-increment value)

How do I get the next auto-increment value from Login so I could use it when populating the Customer table?

I am using SQL Server 12.

I had trouble understanding your table schema. Can you please make it a bit clearer? — Sam, Sep 13 '13 at 08:32
Are you asking how to get the autoincrement-generated id value from newly created `Login` records so you can use them in the other tables? — Sam, Sep 13 '13 at 08:33
What are you using to access the tables and database? Entity Framework? — Sam, Sep 13 '13 at 08:33

score 1 · Answer 1 · edited Sep 10 '20 at 15:50

If you are using a Stored Procedure to handle this logic, then you can do this in one transaction by getting the Scope_Identity value from the newly inserted record, and then using this value to insert into the next table.

Scope_Identity is definitely the way to go here, and you will get the latest ID within the scope of your call to the SP.

If this is a two step process via Entity Framework, then take a look at How can I retrieve Id of inserted entity using Entity framework?

score 0 · Answer 2 · edited Sep 13 '13 at 08:56

0

select isnull(MAX(CustomerID),0)+1 from Customer

edited Sep 13 '13 at 08:56

Chris

8,527
10
34
51

answered Sep 13 '13 at 08:35

Dipak Panchal

36
3

can you please show me the whole statement? is that an sql statement? – kayze Sep 13 '13 at 10:31
@kayze Yes, this is a SQL statement. – TylerH Sep 10 '20 at 15:55

score 0 · Answer 3 · edited Sep 10 '20 at 15:51

0

Try this SQL statement:

SHOW TABLE STATUS LIKE 'Login_Table'

Seek the value of Auto_increment or other details

edited Sep 10 '20 at 15:51

TylerH

20,799
66
75
101

answered Sep 13 '13 at 08:37

internals-in

4,798
2
21
38

score 0 · Answer 4 · edited Sep 10 '20 at 15:51

0

You could use a Guid in your C# code and set that as the CustomerID, (Guid custID = new Guid();). Guids are the type uniqueidentifier in SQL Server.

Are you using Entity Framework? It's a great way to access a SQL database from a .NET application.

edited Sep 10 '20 at 15:51

TylerH

20,799
66
75
101

answered Sep 13 '13 at 08:40

Slouch

637
5
6

sorry i cant use guid the database structure is using autoincrement for login table, i cant modify that – kayze Sep 13 '13 at 10:32

Alex · Answer 5 · 2013-09-13T10:40:28.950

0

If you are using sql queries:

let your inertIntoLoginTable function return an integer which is the SCOPE_IDENTITY or @@Identity

If you are using an ORM like Entity Framework, the id will be set to the object after insertion in the database:

Login.loginID will be the last id inserted after you call dbContext.SaveChanges();

edited Sep 13 '13 at 10:40

answered Sep 13 '13 at 08:47

Alex

5,971
11
42
80

How do I get the next auto increment value for a SQL table and use it?

5 Answers5