how to insert data in two different table [Resolved]

Posted by Gow.Net under Sql Server on 4/30/2012 | Points: 10 | Views : 12059 | Status : [Member] | Replies : 7
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


Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response