-1

I have two tables employee_table and salary_table.

employee_table has emp_id (primary key and identity), emp_name and designation columns, while salary_table has emp_id (foreign key) and salary.

In my C# Windows Forms application, I am inserting emp_name, designation and salary.

While inserting these values, emp_id will be auto-generated.

I want to pass the emp_id value to salary table as the foreign key automatically.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kurinji Nathan Kumar
  • 29
  • 2
  • Welcome to StackOverflow. Please [edit] your question to add a tag for the specific flavour of SQL that you are using. You need to show us what you have tried so we can help you fix it. – Richardissimo Dec 08 '18 at 04:49

1 Answers1

1

What database and which driver are you using? Each different database has a different way of getting the identity column back through your insert statement. For instance for Microsoft SQL Server you do:

INSERT INTO Table(Columns...) 
VALUES (VALUES...);SELECT SCOPE_IDENTITY();

This will return the auto-number column value back for you to be able to use in your child table insert statements as the foreign key values.

Oracle has a different way of doing that. Let us know what database you are using so we can help you...

Enjoy!

Igal Nassi
  • 65
  • 7
  • 1
    Two tips: (1) `@@identity` is a [bad solution](http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value) to that problem. And (2) it's more productive to ask the OP questions in comments to clarify their scenario by editing their question before offering an answer. – Richardissimo Dec 08 '18 at 04:47
  • Sorry, not very experienced in SO, I will use comments as suggested going forward. Was just trying to be helpful, Having said that, you are right that you can do SCOPE_IDENTITY() on SQL and perhaps that would be better practice. Unless session is reused with multiple concurrent statements (haven't seen a good use-case for that scenario yet), @@IDENTITY should do just fine as well. – Igal Nassi Dec 08 '18 at 04:56
  • The article above *demonstrates* an example which does not involve *"concurrent statements"* (whatever you mean by that). Perhaps a different reference would help: https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – Richardissimo Dec 08 '18 at 05:07
  • I think we are getting off topic a bit, but to be clear, unless 2 different threads are not using the same connection concurrently to do the "INSERT ...; SELECT @@IDENTITY;" queries (sharing the same database session), I don't believe @@IDENTITY would have any adverse effect. I have not tested that yet, so I cannot be 100% sure that even 2 concurrent threads as explained above would cause any issues. Regardless, I personally have been executing "INSERT ...; SELECT @@IDENTITY" as a single block on the connection and never had problems. Appreciate the SCOPE_IDENTITY() clarification though! – Igal Nassi Dec 08 '18 at 05:14
  • `@@identity` **is** the basis of the answer you've offered, so discussing it in comments is entirely appropriate. Suggestion: rather than write a comment effectively saying *"it's always worked for me"*, perhaps *try* the simple example in my first link (a scenario also mentioned by the second link). No need for threads to see the problem. Before you say "I don't use triggers"... Given two solutions, one of which *always* does what's required and one which *occasionally* doesn't, I know which I 'd prefer. I rest my case. – Richardissimo Dec 08 '18 at 05:48
  • I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else (like `@@identity`) to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Dec 08 '18 at 07:54
  • Didn't realize I could update the answer before, so the @@IDENTITY is changed to SCOPE_IDENTITY() now. Your link uses multiple inserts prior to calling either one of the identity solutions. My answer recommends calling the identity solution after each insert, hence it will always work. Having said that, moving to SCOPE_IDENTITY() will not hurt and will be clearer for others. So i updated the answer. Thanks for the input. – Igal Nassi Dec 08 '18 at 16:45