December 21, 2020 at 8:09 am
I have a form with fields to fill : NAME_POSTE, NamePrinter, PathPrinter
for example:
when adding these values, I want to insert it into two différents tables:
CREATE TABLE Postes
(
PK_POSTE int NOT NULL PRIMARY KEY
,NAME_POSTE varchar(20) NOT NULL
);
CREATE TABLE Params_Poste
(
FK_POSTE int NOT NULL
,NAME_PARAM varchar(20) NOT NULL
,VALUE_PARAM varchar(20) NOT NULL
,PRIMARY KEY (FK_POSTE, NAME_PARAM)
,FOREIGNI KEY(FK_POSTE)
);
Postes
PK_POSTE ------ NAME_POSTE
1 ----- poste1
Params_Poste
FK_POSTE ---- NAME_PARAM ----- VALUE_PARAM
1 PathPrinter path 1
1 NamePrinter name imp 1
CREATE PROCEDURE myProc_Add
@NamePoste AS varchar(20)
AS
BEGIN
BEGIN
INSERT INTO Postes (NAME_POSTE)
VALUES (@NamePoste)
INSERT INTO Params_Poste(FK_POSTE ,NAME_PARAM, VALUE_PARAM)
VALUES ( ???? )
any help please !!
December 21, 2020 at 8:14 am
It looks to me as though this statement
INSERT INTO Postes (NAME_POSTE)
VALUES (@NamePoste)
will fail, because you have not provided a value for PK_POSTE.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2020 at 8:30 am
the primary key will be generated automatically when inserting new data
and then i will use this id as fk of the other table to insert the rest of data
December 21, 2020 at 8:41 am
the primary key will be generated automatically when inserting new data
and then i will use this id as fk of the other table to insert the rest of data
The DDL you have provided does not show that. Is PK_POSTE supposed to be an IDENTITY column? If not, how is the value generated?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2020 at 8:46 am
Yes you are right.
sorry, I'm adding this in my DDL.
but this is not my problem, my problem is how can i use the data that i sent as a columns values and convert them to a rows data
December 21, 2020 at 9:10 am
The problem (as far as I can see), is knowing the value of PK_POSTE so that you can INSERT the FK rows in Params_Poste, is that right?
If so, SCOPE_IDENTITY() is what you need. I do not have access to SQL Server right now, but something like this:
CREATE PROCEDURE myProc_Add
@NamePoste varchar(20)
,@NAME_PARAM1 varchar(20)
,@VALUE_PARAM1 varchar(20)
,@NAME_PARAM2 varchar(20)
,@VALUE_PARAM2 varchar(20)
AS
DECLARE @PK_POSTE INT;
INSERT Postes (NAME_POSTE)
VALUES (@NamePoste)
SET @PK_POSTE = SCOPE_IDENTITY()
INSERT Params_Poste(FK_POSTE ,NAME_PARAM, VALUE_PARAM)
VALUES (@PK_POSTE, @NAME_PARAM1, @VALUE_PARAM1)
INSERT Params_Poste(FK_POSTE ,NAME_PARAM, VALUE_PARAM)
VALUES (@PK_POSTE, @NAME_PARAM2, @VALUE_PARAM2)
This works, as long as you always wish to INSERT exactly two rows to PARAMS_POSTE. If the number of rows you wish to INSERT varies, the code must be made more sophisticated.
I would also recommend that you include error handling and encapsulate the series of INSERTs in a transaction.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2020 at 10:00 am
Yes I see what you want to say.
i will try like this making the improvements i need.
Thank you so much for your help. it's quite simple on the contrary that I thought.
December 23, 2020 at 2:07 am
You could also use the OUTPUT clause for the first INSERT statement. Check out this article:
https://www.sqlservercentral.com/articles/the-output-clause-for-insert-and-delete-statements
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply