insert simultaneously in 2 tables
-
Dear All,
I am not an expert on T-SQL and I am trying out a small project to learn.
I have encountered a problem, whereby I have 2 tables, 1 containing the header (header_id, header_file, admin_menu_id and admin_submenu_id) and then I have another table called header_details, where I am storing the language details for this header, with the fields being (header_details_id, fk_header_id, header_alt, header_caption and fk_language_id)
Now I want to create a stored proc, first to insert the header and then the header details. I also want that if the header or header details already exist, I just do an update on these tables.
I tried the following code but its not working:-
ALTER Procedure [dbo].[INSERT_Header]
(
@admin_menu_idint,
@admin_submenu_idint,
@header_filevarchar(150),
@header_altvarchar(150),
@header_captionvarchar(200),
@language_idint
@outIDint OUTPUT
)
AS
BEGIN
-- First do a select on the header table to see if this header already exists
DECLARE @count integer = 0
SELECT COUNT(*) as @count
FROM headers
WHERE[fk_admin_menu_id] = @admin_menu_id
AND[fk_admin_submenu_id] = @admin_submenu_id
--if it exists, then update this header
IF @count > 0
BEGIN
UPDATE [headers]
SET [header_file]= @header_file
WHERE [fk_admin_menu_id] = @admin_menu_id
[fk_admin_submenu_id] = @admin_submenu_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@header_file
,@admin_menu_id
,@admin_submenu_id)
-- Get the Inserted Header ID --
SET @outID = SCOPE_IDENTITY()
END
-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @count_details integer = 0
SELECT COUNT(*) as @count_details
FROM header_detail
WHERE[fk_admin_menu_id] = @admin_menu_id
AND[fk_admin_submenu_id] = @admin_submenu_id
AND[fk_language_id] = @language_id
--if it exists, then update this header
IF @count_details > 0
BEGIN
UPDATE [header_detail]
SET[header_alt]= @header_file
,[header_caption] = @header_caption
WHERE [fk_header_id] = @header_id
[fk_language_id] = @language_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@@out_ID
,@header_alt
,@header_caption
,@language_id)
END
END
Can you help me out please?
Thanks a lot for your help and time
Johann
-
What's not working? The only thing I see at a quick glance is that you are trying to insert @@OutID instead of @OutID in the details table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try this:
ALTER Procedure [dbo].[INSERT_Header]
(
@admin_menu_id int,
@admin_submenu_id int,
@header_file varchar(150),
@header_alt varchar(150),
@header_caption varchar(200),
@language_id int
@outID int OUTPUT
)
AS
BEGIN
--Header - Update Existing
Update headers set header_file = ltrim(rtrim(@header_file))
WHERE headers.fk_admin_menu_id = @admin_menu_id
and headers.fk_admin_submenu_id = @admin_submenu_id
and (ltrim(rtrim(header_file)) <> ltrim(rtrim(@header_file)))
--Header - Insert Non Existing
INSERT INTO [headers]
([header_file],[fk_admin_menu_id],[fk_admin_submenu_id])
VALUES(@header_file,@admin_menu_id,@admin_submenu_id)
where not exists (SElect 1 from [headers] DEST where DEST.fk_admin_menu_id = @admin_menu_id
AND DEST.fk_admin_submenu_id = @admin_submenu_id)
select @outID =fk_header_id from [headers] DEST where DEST.fk_admin_menu_id = @admin_menu_id
AND DEST.fk_admin_submenu_id = @admin_submenu_id
--Detail - Update Existing
Update header_detail SET [header_alt] = ltrim(rtrim(@header_file)),
[header_caption] = ltrim(rtrim(@header_caption))
WHERE fk_header_id =@outID
and [fk_admin_menu_id] = @admin_menu_id
AND [fk_admin_submenu_id] = @admin_submenu_id
AND [fk_language_id] = @language_id
and (
ltrim(rtrim([header_alt])) <> ltrim(rtrim(@header_file)) OR
ltrim(rtrim([header_caption])) <> ltrim(rtrim(@header_caption))
 
--Detail - Insert Non Existing
INSERT INTO [header_detail]
([fk_header_id],[header_alt],[header_caption],[fk_language_id])
VALUES(@out_ID,@header_alt,@header_caption,@language_id)
where not exists (SElect 1 from [header_detail] DEST
WHERE DEST.fk_header_id =@outID and DEST.fk_admin_menu_id = @admin_menu_id
AND DEST.fk_admin_submenu_id = @admin_submenu_id and DEST.fk_language_id = @language_id)
End
-
Hi Sreejith,
I tried your code and got the following errors:-
Msg 156, Level 15, State 1, Procedure INSERT_Header, Line 24
Incorrect syntax near the keyword 'where'.
Msg 156, Level 15, State 1, Procedure INSERT_Header, Line 42
Incorrect syntax near the keyword 'INSERT'.
Msg 137, Level 15, State 2, Procedure INSERT_Header, Line 44
Must declare the scalar variable "@out_ID".
Also can I ask the following:-
1) What does DEST do?
2) Will this do what I described in the explanation? As I am seeing from this SQL, there is no IF and ELSE statements......
As I said I am still a beginner and do not understand a lot of syntax
Thanks for your help
Johann
-
1. DEST is an alias for your Destination table (headers in step 2 and header_detail in step 5)
2.It will do what you were trying to do. you don't need to use if and else all the time.
Here is the updated Code. I have pasted the Create statement for the table as well so that if its different you can make appropriate changes.
Table script:
Create table headers (header_id int, header_file varchar(150),
fk_admin_menu_id int,fk_admin_submenu_id int)
Create table header_details(header_details_id int,
fk_header_id int,
header_alt varchar(150),
header_caption varchar(200),
fk_language_id int)
SP script:
alter Procedure [dbo].[INSERT_Header]
(
@admin_menu_id int,
@admin_submenu_id int,
@header_file varchar(150),
@header_alt varchar(150),
@header_caption varchar(200),
@language_id int,
@outID int OUTPUT
)
AS
BEGIN
--Header - Update Existing
Update headers set header_file = ltrim(rtrim(@header_file))
WHERE headers.fk_admin_menu_id = @admin_menu_id and headers.fk_admin_submenu_id = @admin_submenu_id and (ltrim(rtrim(header_file)) <> ltrim(rtrim(@header_file)))
--Header - Insert Non Existing
INSERT INTO [headers]([header_file],[fk_admin_menu_id],[fk_admin_submenu_id])
select @header_file,@admin_menu_id,@admin_submenu_id
where not exists (SElect 1 from [headers] DEST
where DEST.fk_admin_menu_id = @admin_menu_id AND DEST.fk_admin_submenu_id = @admin_submenu_id)
select @outID =header_id from [headers] DEST where DEST.fk_admin_menu_id = @admin_menu_id
AND DEST.fk_admin_submenu_id = @admin_submenu_id
--Detail - Update Existing
Update header_detail SET [header_alt] = ltrim(rtrim(@header_file)),
[header_caption] = ltrim(rtrim(@header_caption))
WHERE fk_header_id =@outID
and [fk_admin_menu_id] = @admin_menu_id
AND [fk_admin_submenu_id] = @admin_submenu_id
AND [fk_language_id] = @language_id
and (ltrim(rtrim([header_alt])) <> ltrim(rtrim(@header_file)) OR ltrim(rtrim([header_caption])) <> ltrim(rtrim(@header_caption)))
--Detail - Insert Non Existing
INSERT INTO [header_detail]
([fk_header_id],[header_alt],[header_caption],[fk_language_id])
Select @outID,@header_alt,@header_caption,@language_id
where not exists (SElect 1 from [header_detail] DEST
WHERE DEST.fk_header_id =@outID and DEST.fk_admin_menu_id = @admin_menu_id
AND DEST.fk_admin_submenu_id = @admin_submenu_id and DEST.fk_language_id = @language_id)
End
Hope this answers your question and helps you understand the code.
Thanks
Sreejith
-
Johann,
Since you're new, the error at lines 24 and 42 was because you cannot have a WHERE clause in an INSERT/VALUE statement. Sreejith corrected that by changing it to an INSERT/SELECT.
The error at line 44 was a simple type-o which was also corrected.
Sreejith is spot on... you don't need IF just because you want the code to make a decision... the WHERE clauses will suffice in this instance. Nothing will get updated or inserted if the WHERE conditions aren't correct for the data. For example, a header will only be updated when the variables match what's in the columns of the header which means that a record must already exist. Conversely, the INSERT of a new header will occur only when the NOT EXISTS comes into play which also means that it wasn't there for the preceding UPDATE to match on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Wow
Thanks a lot guys, its nice to learn new things!
I did not know that the Update will be ignored if the data does not match!
Another question, is it best to take these decisions in the SQL or should I do it in code?
Johann
-
The update isn't really ignored if there's no match... it just doesn't update anything although it is executed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hi Sreejith Sreedharan
I tried your stored procedure finally, since I had some problems with my laptop, however I got the following errors:-
in this piece of code:-
--Detail - Update Existing
Update header_detail SET [header_alt] = ltrim(rtrim(@header_alt)),
[header_caption] = ltrim(rtrim(@header_caption))
WHERE fk_header_id = @outID
and [fk_admin_menu_id] = @admin_menu_id
AND [fk_admin_submenu_id] = @admin_submenu_id
AND [fk_language_id] = @language_id
and (ltrim(rtrim([header_alt])) ltrim(rtrim(@header_file)) OR ltrim(rtrim([header_caption])) ltrim(rtrim(@header_caption)))
I got an error:-
Msg 207, Level 16, State 1, Procedure INSERT_Header, Line 41
Invalid column name 'DEST.fk_admin_menu_id'.
Msg 207, Level 16, State 1, Procedure INSERT_Header, Line 42
Invalid column name 'DEST.fk_admin_submenu_id'.
Can you please help me some more?
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply