SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.2 seconds. Powered By: Snitz Forums 2000