March 27, 2009 at 8:55 am
HI, i have some problem couse i dont know how to make stored procedure that will make insert in 2 tables.
The firs table is called Vraboten and the columns are:
Vraboten_ID int, --->PK
Ime nvarchar(50),
Prezime nvarchar(50),
EMBG bigint,
Adresa nvarchar(50),
Grad nvarchar(50),
ID_Honorar int --->FK
And the second table is called Honorar and the columns are:
Honorar_ID int, --->PK
ID_Mesec int,
ID_Tip int,
BrutoHonorar decimal,
PersonalenDanok nvarchar(50),
NetoHonorar decimal
The relatinship is FK_Vraboten_Honorar.
The problem is that i want to make stored procedure that will make insert in both tables, and i don't know how to make it. If you have any ideas please help.
Thanks, Vasko
March 27, 2009 at 9:35 am
It really depends on what you need to do. Let's assume a row into each table. You create a proc with variables for each of the columns, except the two PK's. Insert into the second table and then the first table. Use SCOPE_IDENTITY to capture the ID of the second table for use in the first.
It gets more complicated if you want to work with sets of data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2009 at 9:43 am
Can you be more specific as to what the problem is? Is it just the issue of getting the ID from one table to insert into the second table? Or is there more to it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 9:52 am
The problem is that in my application where i plan to use this stored procedure i use one button that when you click it it has to make insert in both tables but i need the id from the second table to make the insert in the first.
I have some text fields that i have to put something in that and after that i want to make the insert.
Hope this was helpfull 🙂
March 27, 2009 at 9:57 am
vasko102 (3/27/2009)
The problem is that in my application where i plan to use this stored procedure i use one button that when you click it it has to make insert in both tables but i need the id from the second table to make the insert in the first.I have some text fields that i have to put something in that and after that i want to make the insert.
Hope this was helpfull 🙂
If you are doing the inserts in this order then it would make more sense if the FK Vraboten_ID was in the second table, rather than the other way round.
Then you can use SCOPE_IDENTITY to retireve the id of the last insert
March 27, 2009 at 9:59 am
If you're inserting one row at a time in each, take a look at "Scope_Identity" in Books Online. See if you can use that for what you need.
If not, take a look at "Output" in Books Online, with reference to Insert statements. That's more powerful, but takes a bit more work.
One or the other will get you what you need. Take a look at them, see if you can figure them out. If you run into problems/questions/concerns with them, ask them here and we'll see what we can do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 10:07 am
ok, i'll try to use your advices and tell you later if i have any success
thanks
March 27, 2009 at 4:26 pm
You might do the following , i had tried in my earlier projects. My assumption is as follows .
1>You might take data from any of your front end. i.e. Asp.net , php etc.
2>Vraboten_ID is Auto increment , each time by onE and you don't need to insert it manually.
3>Data is already enterd in the second table Honorar having field is Auto increment , each time by ont.and you don't need to insert it.
4>All you have to do is to make sure that entry is entered in tne Vraboten at field ID_Honorar int. This is achived by @@IDENTITY
Your desired name for the stored procedure
solution
create PROCEDURE [Person_Insert]
--your desired name
@Ime nvarchar(50),
@Prezime nvarchar(50),
@EMBG bigint,
@Adresa nvarchar(50),
@Grad nvarchar(50),
--- now all the details are filled similer to column name & their respective datatypes
--Don't be nervus , its just all the details you provided are written with @ as aprefix to used in stored procedure
--As I earlier mentioned , assumed both the primary keys of table are auto increment NO NEED to enter them
---time for entering data into second table Honorar
@ID_Mesec int,
@ID_Tip int,
@BrutoHonorar decimal,
@PersonalenDanok nvarchar(50),
@NetoHonorar decimal
AS
--Start the transaction
BEGIN TRANSACTION
--be on safe side ,use TRANSACTION to enter data only for critical data
DECLARE @Honorar_ID int
--now we will fill up the first Honorar table since ,it's primary key must be enterd in Vraboten table
INSERT INTO Honorar (ID_Mesec,ID_Tip,BrutoHonorar,PersonalenDanok, NetoHonorar )
VALUES(@ID_Mesec ,@ID_Tip ,@BrutoHonorar ,@PersonalenDanok,@NetoHonorar)
--all your fields are entered with their respected as specified
SET @Honorar_ID = @@IDENTITY
--this refers the latest auto incremented value table.
--now following line make sure the data is entred properly
-- Roll back the transaction if there are any errors, jsut in case.
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Honorar .', 16, 1)
RETURN
END
--now time to insert into our main table Vraboten
-- Next Step
INSERT INTO Vraboten ( Ime , Prezime , EMBG , Adresa,Grad ,ID_Honorar)
VALUES (@Ime ,@Prezime ,@EMBG ,@Adresa ,@Grad ,@Honorar_ID)
SELECT @@IDENTITY
-- Roll back the transaction if there are any errors
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Vraboten ', 16, 1)
RETURN
END
COMMIT
Relax it's these are just simple queries to enter the data.
Problem you might face
1> not enterd correctely
-> make sure you had written the field names exactely as your data is going to be enterd in it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply