How to get auto incremented id at the same time when inserted data in sqlClient?

Question

I am using stored procedure to insert data. I have 2 tables one to store the customer details and the other one is used to store the payment history. So when I press insert details button on my form this stored procedure is called. I have to insert data in both table at the same time.

[dbo].[CustomerDetails] has primary key CustomerId which is set to Auto Increment.

So at the time of insertion I want this CustomerId to Set as RecordId in [dbo].[PaymentHistory]

NOTE: All other information can be same, however each account is differentiated by primary key CustomerId in CustomerDetails

INSERT INTO [dbo].[CustomerDetails] ([CustomerName], [FatherName], [Cnic], [ContactNo], [Address], [City], [StartDate], [EndDate],[SamanDesc], [Tola], [Masha], [Rati], [Location], [Amount], [Percentage], [Months], [Days], [Status]) 
VALUES (@CustomerName, @FatherName, @Cnic, @ContactNo, @Address, @City, @StartDate, @EndDate, @SamanDesc, @Tola, @Masha, @Rati, @Location, @Amount, @Percentage, @Months, @Days, @Status);

INSERT INTO [dbo].[PaymentHistory] ([RecordId], [DatePaid], [Amount], [AmountPaid], [Profit])
VALUES (@id, @StartDate, @Amount, 0, 0);

One solution is do a select query right after insert [CustomerDetails] and get the latest inserted id from the table. — MasoodRehman, Sep 22 '17 at 11:08
Use the [`OUTPUT` clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql). — Oliver, Sep 22 '17 at 11:12
https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — MasoodRehman, Sep 22 '17 at 11:17

score 2 · Answer 1 · answered Sep 22 '17 at 11:14

For MSSQL, there is SCOPE_IDENTITY(), which appears quite safe for use in stored procedures, even though I never used it myself, so I cannot comment on any oddities it might have.

https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql

score 1 · Answer 2 · answered Sep 22 '17 at 19:25

Hi guys I solved my problem using SCOPE_IDENTITY. For most applications we need to return the recent ID for which we can use @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT(). Those who don't know what it is they can go to this link to understand these

https://www.codeproject.com/Articles/103610/Difference-between-IDENTITY-SCOPE-IDENTITY-IDENT-C

Code goes as follows:

DECLARE @id int;

INSERT INTO [dbo].[CustomerDetails] ([CustomerName], [FatherName], [Cnic], [ContactNo], [Address], [City], [StartDate], [EndDate],[SamanDesc], [Tola], [Masha], [Rati], [Location], [Amount], [Percentage], [Months], [Days], [Status]) 
VALUES (@CustomerName, @FatherName, @Cnic, @ContactNo, @Address, @City, @StartDate, @EndDate, @SamanDesc, @Tola, @Masha, @Rati, @Location, @Amount, @Percentage, @Months, @Days, @Status);

SET @id = (SELECT SCOPE_IDENTITY());

INSERT INTO [dbo].[PaymentHistory] ([RecordId], [DatePaid], [Amount], [AmountPaid], [Profit])
VALUES (@id, @StartDate, @Amount, 0, 0);

score 0 · Answer 3 · answered Sep 22 '17 at 11:11

0

If your database is MySQL, use

select last_insert_id() from dual;

This will retrieve this information.

answered Sep 22 '17 at 11:11

Marc Vanhoomissen

395
2
12

I am using SqlClient – Ezaz Sep 22 '17 at 18:46
OK, I do not know sqlclient but due to its name, I suspect this software allows tyou to connect to a database. My question is then which database do you connect to? – Marc Vanhoomissen Sep 23 '17 at 09:13
Well it is more likely used to handle databases for desktop applications. – Ezaz Sep 23 '17 at 14:32

How to get auto incremented id at the same time when inserted data in sqlClient?

3 Answers3