how to insert data in two different table [Resolved]

Posted by Gow.Net under Sql Server on 4/30/2012 | Points: 10 | Views : 14257 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i create two table in SQL any one tell me how to insert the value in that two tables \

create table Person(p_id int not null primary key,Fname varchar(50),Lname varchar(50),address varchar(50))
create table orders(o_id int not null primary key,oname varchar(50),Qty int,p_id int constraint g foreign key(p_id) references Person(p_id),prize int)

gowthaman8870226416

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 50

0	This is for an Idea...! Change the code as you required create table Person ( p_id int IDENTITY(1,1) not null primary key, Fname varchar(50), Lname varchar(50), address varchar(50) ) go create table orders ( o_id int IDENTITY(1,1) not null primary key, oname varchar(50), Qty int, p_id int foreign key references Person(p_id), prize Int ) go Create Proc Usp_Orders_Insert ( @Param_Fname Varchar(50), @Param_Lname Varchar(50), @Param_Address Varchar(50), @Param_oname Varchar(50), @Param_Qty Int, @Param_prize Int ) As Begin Set Nocount On Declare @PersonID Int Begin Try Begin Tran Insert Person(Fname, Lname, [address]) Values(@Param_Fname, @Param_Lname, @Param_Address) Select @PersonID = @@Identity Insert orders(oname, Qty, p_id, prize) Values(@Param_oname, @Param_Qty, @PersonID, @Param_prize) Commit End Try Begin Catch Declare @Message Varchar(Max) Select @Message = 'Error : ' + ERROR_MESSAGE() + CHAR(10) + 'Line No: '+ Cast(ERROR_LINE() as Varchar(10)) Raiserror(@Message,16,1) Rollback End Catch End Go Exec Usp_Orders_Insert 'Gow','Net','Chennai','Notbook',2,100 Go Note: - "Person.p_id" and "orders.o_id" as an IDENTITY (or) Change the procedure as you required! Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

This is for an Idea...!

Change the code as you required

create table Person

(

p_id int IDENTITY(1,1) not null primary key,

Fname varchar(50),

Lname varchar(50),

address varchar(50)

)

go



create table orders

(

o_id int IDENTITY(1,1) not null primary key,

oname varchar(50),

Qty int,

p_id int foreign key references Person(p_id),

prize Int

)

go



Create Proc Usp_Orders_Insert

(

@Param_Fname	Varchar(50),

@Param_Lname	Varchar(50),

@Param_Address	Varchar(50),

@Param_oname	Varchar(50),

@Param_Qty		Int,

@Param_prize	Int

)

As

Begin

	Set Nocount On

	

	Declare @PersonID	Int

	

	Begin Try

		Begin Tran	

			

		Insert Person(Fname, Lname, [address]) Values(@Param_Fname, @Param_Lname, @Param_Address)

		Select @PersonID = @@Identity 

	

		Insert orders(oname, Qty, p_id, prize) Values(@Param_oname, @Param_Qty, @PersonID, @Param_prize)

		

		Commit

			

	End Try

	Begin Catch

		Declare @Message Varchar(Max)

		Select @Message = 'Error : ' + ERROR_MESSAGE() + CHAR(10) + 'Line No: '+ Cast(ERROR_LINE() as Varchar(10))

		Raiserror(@Message,16,1)

		Rollback		

	End Catch

End

Go

Exec Usp_Orders_Insert 'Gow','Net','Chennai','Notbook',2,100

Go

Note:
- "Person.p_id" and "orders.o_id" as an IDENTITY
(or)
Change the procedure as you required!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

0	Hi You can use Stored procedure to write any data (INSERT/UPDATE/DELETE) data on this tables within TRANSACTION. The whole process will be ROLLED back In-case of any failure in any of this table! Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Gow.Net on: 4/30/2012 [Member] Starter | Points: 25

0	sir please tell me that SQL Query and sir what the use of foreign key gowthaman8870226416 Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Gow.Net on: 4/30/2012 [Member] Starter | Points: 25

0	ya sir thank you very much it possible to explain this coding because today only i start studying stored procedure gowthaman8870226416 Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

0	If you are new to Stored procedure then... Please proceed with the following link http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.100%29.aspx Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Gow.Net on: 4/30/2012 [Member] Starter | Points: 25

0	sir please explain this line Raiserror(@Message,16,1) what is that 16,1 gowthaman8870226416 Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 4/30/2012 [Member] [MVP] Silver | Points: 25

0	RAISERROR is used to return Messages to the applications with the same format as a system Error or Messages (Its depend on Severity Value (i.e: 10,16,... Etc) To Raise an Error Begin Try Select 1/0 End Try Begin Catch Raiserror('Error Occurred!',16,1) End Catch Go To Raise a Message Begin Try Select 1/0 End Try Begin Catch Raiserror('Error Occurred!',10,1) End Catch Go For further reading... http://msdn.microsoft.com/en-us/library/ms177497(v=sql.100).aspx Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gow.Net, if this helps please login to Mark As Answer. \| Alert Moderator

RAISERROR is used to return Messages to the applications with the same format as a system Error or Messages (Its depend on Severity Value (i.e: 10,16,... Etc)

To Raise an Error

Begin Try

	Select 1/0

End Try

Begin Catch

	Raiserror('Error Occurred!',16,1)

End Catch

Go

To Raise a Message

Begin Try

	Select 1/0

End Try

Begin Catch

	Raiserror('Error Occurred!',10,1)

End Catch

Go

For further reading...
http://msdn.microsoft.com/en-us/library/ms177497(v=sql.100).aspx

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts