• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

Help with transaction; Related tables

I am creating a transaction.   I have done this before with unrelated tables.   However, now I have related table.  Say I have an "organization" table.   Then I also have a "member" table, which has a foreign key to the "organization" table.   If I add a new organization, I need the primary key of that record to then use on the INSERT for the "member" table.   If this was not within a transaction, I would use SELECT Scope_Identity() and .ExecuteScalar to obtain the Primary Key.  

How do I do this same thing within a transaction?
0
HLRosenberger
Asked:
HLRosenberger
  • 4
  • 2
3 Solutions
 
ste5anSenior DeveloperCommented:
Yes. You can use it in a transaction. Why do you mention .ExecutScalar? Consider using a single T-SQL batch or even better a stored procedure to do this.
0
 
HLRosenbergerAuthor Commented:
I mention .ExecuteScalar because that's how I got back the Primary key; that the only way I know how get it.  

So, are you saying I can use SELECT Scope_Identity()  in a transaction?  How exactly?
0
 
ste5anSenior DeveloperCommented:
E.g.

INSERT INTO tableName () VALUES ();

DECLARE @ID INT = SCOPE_IDENTITY();

INSERT INTO otherTableName () VALUES ( @ID.. );

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Deepak ChauhanSQL Server DBACommented:
@I would use SELECT Scope_Identity()

Is primary key a Identity column in Organization table ?

if yes then you can take the advantage of Scop_identity() if not then you can use output clause in T-SQl script.
0
 
HLRosenbergerAuthor Commented:
yes. it's an identity column.
0
 
HLRosenbergerAuthor Commented:
How do I do this:  DECLARE @ID INT = SCOPE_IDENTITY();

if it's not a stored proc?
0
 
HLRosenbergerAuthor Commented:
I see that I can still do rh.ID = command.ExecuteScalar() even within the transaction

I got it working.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now