inserting in 2 tables simultaneously

Question

Post reply

inserting in 2 tables simultaneously

Johann Montfort

Hall of Fame

Points: 3123
More actions
October 31, 2006 at 11:12 pm

#115247

Dear All,
I wish to insert a header image in 2 tables simultaneously, by getting the header_id from the first insert and using that to do the second insert.
At the moment I came up with this stored proc, however it is not working fine:-
ALTER Procedure [dbo].[INSERT_Header]
(
@admin_menu_idint,
@admin_submenu_idint,
@header_filevarchar(150),
@header_altvarchar(150),
@header_captionvarchar(200),
@language_idint,
@fk_header_idint OUTPUT,
@outidint OUTPUT
)
AS
BEGIN
-- Insert the into headers --
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 @fk_header_id = SCOPE_IDENTITY()
END
IF @fk_header_id IS NOT NULL
BEGIN
-- Insert the into headers --
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@fk_header_id
,@header_alt
,@header_caption
,@language_id)
-- Get the Inserted header_detail --
SET @outid = SCOPE_IDENTITY()
END
Can you guys help me out?
Thanks for your help and time
Johann

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ijaz SSCommitted Points: 1912 More actions · Answer 1

Hi,

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,

@fk_header_id int OUTPUT,

@outid int OUTPUT

)

AS

BEGIN

-- Insert the into headers --

INSERT INTO [headers]

(

[header_file]

,[fk_admin_menu_id]

,[fk_admin_submenu_id]

&nbsp

VALUES

(

@header_file

,@admin_menu_id

,@admin_submenu_id

&nbsp

-- Get the Inserted Header ID --

SET @fk_header_id = SCOPE_IDENTITY()

IF @fk_header_id IS NOT NULL

BEGIN

-- Insert the into headers --

INSERT INTO [header_detail]

(

[fk_header_id]

,[header_alt]

,[header_caption]

,[fk_language_id]

&nbsp

VALUES

(

@fk_header_id

,@header_alt

,@header_caption

,@language_id

&nbsp

-- Get the Inserted header_detail --

SET @outid = SCOPE_IDENTITY()

END

I think this will slove your problem

cheers

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 2

Johann,

Have you considered using an insert trigger in the headers table?

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 3

Hi Journey man,

To tell you the truth I have never used triggers, I am new in SQL Server.

Another thing, today I had a chat with a fellow colleague, and he told me its better not to use Identity fields in the database, and just create your own unique number, because when you need to move the database to another server, there can be conflicts with these identity fields, since they can change their unique number once exported, and as so, if there are any linked tables, you loose all the references.

What do you guys think?

Will1922 SSCertifiable Points: 7189 More actions · Answer 4

Gotta disagree with your fellow colleague. When you export the data, just turn off the identity column on the destination table and then turn it back on afterwards. Make sure to export the identity columns also. I have a nightly job that exports about 10,000,000 million rows nightly that keeps the identity columns. No problems.

I would much rather let SQL Server handle the identity columns then have to program for each new table that I create.

Live to Throw
Throw to Live
Will Summers

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 5

Hi Will

Thanks for your reply.

I will keep on using the Identity fields as you suggested, since its much easier to let SQL Server handle the dirty work for me.

Thanks again

Johann