Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Question about sql transactions
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fady10
Starting Member

Egypt
7 Posts

Posted - 09/25/2012 :  17:09:23  Show Profile  Reply with Quote
I'm making a sql transaction. First, I'm inserting a record then I use the user id that I inserted using the first statement to insert it into an other table ..

How can I make this work in the same TRAN ?

Thanks in Advance

iFunny; the funniest blog ever ! - funny comics & pictures

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/25/2012 :  17:21:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
BEGIN TRAN
<do Stuff>
COMMIT

-Chad
Go to Top of Page

fady10
Starting Member

Egypt
7 Posts

Posted - 09/25/2012 :  17:26:17  Show Profile  Reply with Quote
I know :) the problem is about <do stuff> ..
for example :
BEGIN TRAN
<statement 1 INSERT>
<statement 2 INSERT>
COMMIT

<statement 2 INSERT> is using a field that I inserted using <statement 1 INSERT> .. How can I use that field ? and Sql server will make me able to do this ?

iFunny; the funniest blog ever ! - funny comics & pictures
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/25/2012 :  17:30:54  Show Profile  Visit chadmat's Homepage  Reply with Quote
Is the id an Identity column? If so, use SCOPE_IDENTITY()

-Chad
Go to Top of Page

fady10
Starting Member

Egypt
7 Posts

Posted - 09/25/2012 :  17:43:30  Show Profile  Reply with Quote
I'll use it like this ?
BEGIN TRAN
INSERT INTO [Person] VALUES (15,'Sam','2345',0.0,'2011-04-16 00:00:00.000');
INSERT INTO [Tree] VALUES (@@IDENTITY,'5','','','','','');
COMMIT TRAN
GO



iFunny; the funniest blog ever ! - funny comics & pictures
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/25/2012 :  17:54:45  Show Profile  Visit chadmat's Homepage  Reply with Quote
Use Scope_Identity, not @@Identity.

I would do more like:

DECLARE @id INT

BEGIN TRAN
INSERT INTO [Person] VALUES (15,'Sam','2345',0.0,'2011-04-16 00:00:00.000')
SELECT @id = SCOPE_IDENTITY()
INSERT INTO [Tree] VALUES (@id,'5','','','','','')
COMMIT TRAN

-Chad

Go to Top of Page

fady10
Starting Member

Egypt
7 Posts

Posted - 09/25/2012 :  18:30:12  Show Profile  Reply with Quote
Thanks man :) .. I'll give it a try

iFunny; the funniest blog ever ! - funny comics & pictures
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/25/2012 :  18:34:02  Show Profile  Visit chadmat's Homepage  Reply with Quote
No Problem.

-Chad
Go to Top of Page

fady10
Starting Member

Egypt
7 Posts

Posted - 09/26/2012 :  19:33:45  Show Profile  Reply with Quote
It works ! Thanks man :)

iFunny; the funniest blog ever ! - funny comics & pictures
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000