how to retrieve identity auto number of one table to insert in another table

Question

1.44/5 (2 votes)

See more:

I have 2 tables

Purchase has field InvoiceNo which is identity auto number

PurchaseProduct has fields PurchaseProduct_No which is identity auto number and InvoiceNo from Purchase table

For concurrency I plan to insert like this

C#

sqlTran = conn.BeginTransaction();
try
{
    //insert statments for Purchase
    //insert statments for PurchaseProduct
    sqlTran.Commit();
}
catch (SqlException ex)
{
    sqlTran.Rollback();

}

but how do I get InvoiceNo value of Purchase table to insert InvoiceNo in PurchaseProduct table since it is autonumber?

Posted 16-Jul-13 5:09am

deva936

Updated 16-Jul-13 5:13am

Johnny J.

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Johnny J. · Answer 1 · 2013-07-16T05:15:00

Solution 2

You do:

myQuery = "INSERT INTO fubar (fu) VALUES ('bar'); SELECT @@IDENTITY;"

Then you execute the query with ExecuteScalar (NOT ExecuteNonQuery) and cast the result of the query to an int64.

There you've got your identity to insert in the other table.

Posted 16-Jul-13 5:15am

Johnny J.

Comments

Mike Meinz 16-Jul-13 11:30am

There are cases where @@IDENTITY is not reliable. SCOPE_IDENTITY() is preferred.

Here is a quote from the documentation for SCOPE_IDENTITY():
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope.

jemaritn80 17-Jul-13 8:21am

Thanks, Mike. I didn't know that about @@IDENTITY.

Mike Meinz · Answer 2 · 2013-07-16T05:13:00

Solution 1

See the documentation for SCOPE_IDENTITY.

SCOPE_IDENTITY[^]

Example

SQL

CREATE TABLE [dbo].[T1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[field1] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T3](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_REF] [int] NOT NULL,
	[field1] [nchar](10) NOT NULL
) ON [PRIMARY]
GO

Note: These two SQL statements can be sent to the SQL Server in one command string.

SQL

insert into T1 (field1) VALUES('XX');
insert into T3 (ID_REF,field1) VALUES(SCOPE_IDENTITY(),'YY');

Posted 16-Jul-13 5:13am

Mike Meinz

Updated 16-Jul-13 5:32am

v3

Comments

deva936 17-Jul-13 0:19am

my insert statement or PurchaseProduct is like this

"INSERT INTO PurchaseProduct" +
"(" +
" Purchase_InvoiceNo" +
.
.
.
")" +
"Values" +
"(" +
" @Purchase_InvoiceNo" +
.
.
.
");";

and @Purchase_InvoiceNo is connected with DataColumn "Purchase_InvoiceNo"

DataRow drPurchaseProduct = dtPurchaseProduct.NewRow();
drPurchaseProduct["Purchase_InvoiceNo"] = Purchase_InvoiceNo;
.
.
.
dtPurchaseProduct.Rows.Add(drPurchaseProduct);

so i have to insert Scope_Identity() into DataTable first

is it possible to do that ?

forgive me if it is a silly question for you

Mike Meinz 17-Jul-13 8:24am

You are doing your inserts in a SQL Transaction so they all get put into the database or none get put into the database. That's a good practice.

I am not exactly clear on how you are inserting rows into the PurchaseProduct table because you mention both an INSERT statement and updating a Data Table Rows.Add method.

I suggest you first INSERT the Purchase table row using ExecuteScalar with CommandText like this:
INSERT INTO Purchase(col1,col2,col3) Values(@col1,@col2,@col3);SELECT SCOPE_IDENTITY();

Then, use the return value from the ExecuteScalar to set the Purchase_InvoiceNo value into each row of your dtPurchaseProduct Data Table or as a SQLParameter to an INSERT statement.

jemaritn80 · Answer 3 · 2013-07-16T05:16:00

Solution 3

Use SqlCommand "INSERT INTO table-name (col1, col2, col3, ...) VALUES (val1, val2, val3,...);Select CAST(@@Identity AS INT);"
and then ExecuteScalar and cast result as an int.
The CAST(... AS INT) is only necessary depending on which database/version you're using.

Posted 16-Jul-13 5:16am

jemaritn80